代码之家 › 专栏 › 技术社区 › ÇAÄrÄ± Keskin

SQL-删除(消除)没有数据的列

null sql-server

ÇAÄrÄ± Keskin · 技术社区 · 8 年前

我想知道; 在SQl中,是否可以不带无数据(或零值)的列?

Select * from PLAYER_TABLE where PLAYER_NAME='cagri'

它只带来了一排。因为只有一个球员的名字叫“cagri”。

例如,统计信息有30列。得分篮板比赛分钟失误等。。。。

Score=2
Rebound=0
PlayedMinutes=2
Fauls=0

调用查询时,我只想看到[分数]和[播放分钟数]列。

有可能吗?

2 回复 | 直到 8 年前

RoMEoMusTDiE 8 年前

您可以在 stored procedure in SQL

DDL公司

create table usr_testtable 
(player varchar(30),col1 float, col2 float, col3 float, col4 float)

insert into usr_testtable 
values ('Jordan',10,20,3,0)

转换为存储过程

declare @playername varchar(30) = 'Jordan'   --- pass this value

declare @ctr smallint = 2  -- start from ordinal 2
declare @maxctr smallint = (SELECT max(ORDINAL_POSITION)
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE TABLE_NAME = 'usr_testTable')

declare @columns varchar(max) = ''
declare @columnswithvalues varchar(max) = ''

declare @coltocheck varchar(30)


declare @mysql nvarchar(max)
declare @coloutput varchar(30) 

while @ctr <= @maxctr  
    begin

            SELECT @coltocheck = COLUMN_NAME
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE TABLE_NAME = 'usr_testTable'
                            and ORDINAL_POSITION = @ctr


          set @mysql = N'select @output = ' + @coltocheck + ' from usr_testTable where player =''' + @playername + ''' and cast(' + @coltocheck +' as float) > 0'

          EXECUTE sp_executesql    @mysql,N'@output int OUTPUT',@output = @coloutput OUTPUT;



          if @coloutput > 0 
             begin
              set @columns = coalesce(@columns + ',' + @coltocheck,@columns)
              set @columnswithvalues =  coalesce(@columnswithvalues + char(13) + char(10) + @coltocheck + ' : '  + @coloutput,@columnswithvalues)  --- text form
            end

          set @coloutput = ''

          set @ctr= @ctr + 1

    end

-- final result in table format

set @mysql = N'select player' + @columns + ' from usr_testTable where player =''' + @playername + ''' '
EXECUTE sp_executesql @mysql

-- final result in text format appended with columnname
select @columnswithvalues    -- format to display in text

Pang Ajmal PraveeN 8 年前

首先创建动态SQL以选择表中的所有列名称 PLAYER_TABLE 除了 PLAYER_NAME ,然后从 PLAYER\u表 进入表格 PLAYER_TABLE1 ,然后您可以搜索值<&燃气轮机;0并在第二个动态SQL中选择此列。

DROP TABLE PLAYER_TABLE1

DECLARE @Player NVARCHAR(50);
DECLARE @columns NVARCHAR(max); 
DECLARE @sql NVARCHAR(max);
DECLARE @columns2 NVARCHAR(max);
DECLARE @sql2 NVARCHAR(max);
SET @player='cagri'
SET @columns = Stuff((SELECT ',' 
                             + Quotename(Rtrim(Ltrim(x.columns))) 
                      FROM   (SELECT COLUMN_NAME as columns FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME= 'PLAYER_TABLE' and COLUMN_NAME<>'PLAYER_NAME' ) AS x 
                      ORDER  BY X.columns 
                      FOR xml path('')), 1, 1, ''); 
SET @sql = N' SELECT 
        PLAYER_NAME, Un_Pivot.Field, Un_Pivot.Value
    INTO PLAYER_TABLE1
    FROM
        (
         SELECT * FROM PLAYER_TABLE
        ) Data
    UNPIVOT
        (
         Value FOR Field IN ('+@columns+')
        ) AS Un_Pivot'; 
EXECUTE sp_executesql @sql;

SET @columns2 = Stuff((SELECT ',' 
                             + Quotename(Rtrim(Ltrim(y.Field))) 
                      FROM   (SELECT Field FROM PLAYER_TABLE1 WHERE VALUE<>0 AND PLAYER_NAME=@Player) AS y
                      ORDER  BY y.Field 
                      FOR xml path('')), 1, 1, '');
SET @sql2 = N'SELECT PLAYER_NAME,'+@columns2+'FROM PLAYER_TABLE WHERE PLAYER_NAME='+char(39)+@Player+char(39);
EXECUTE sp_executesql @sql2