sql - 如果整个列中的值为空，则从输出中删除

Question

我不确定是否可行，但如果有人可以帮助我，我将不胜感激。

如果所有输出行的列都是空白的，则不应出现在输出中。

示例数据:

DECLARE @T TABLE
(

BaseVehicle VARCHAR (50),   
SubModel    VARCHAR (50),
Make    VARCHAR (50),
Years   VARCHAR (50),
FromYear VARCHAR (50),  
ToYear  VARCHAR (50)
)


INSERT @T

SELECT '1979 Ford LTD','','FORD','1979','','' UNION ALL     
SELECT '1979 Ford LTD','','FORD','1979','','2005' UNION ALL         
SELECT '1979 Ford LTD','','FORD','1979','','' UNION ALL         
SELECT '1979 Ford LTD','','FORD','1979','','' 

预期输出

BaseVehicle   |Make|year|toyear
1979 Ford LTD |ford|1979|
1979 Ford LTD |ford|1979|2005
1979 Ford LTD |ford|1979|
1979 Ford LTD |ford|1979|

谢谢

Answer 1

检查所有单元格的计数为空的单元格计数。如果两者匹配，则整个列为空。然后从选择中保留该列。

查询

create table #T
(
   BaseVehicle VARCHAR (50),   
   SubModel    VARCHAR (50),
   Make    VARCHAR (50),
   Years   VARCHAR (50),
   FromYear VARCHAR (50),  
   ToYear  VARCHAR (50)
)

INSERT into #T 
SELECT '1979 Ford LTD','','FORD','1979','','' UNION ALL     
SELECT '1979 Ford LTD','','FORD','1979','','2005' UNION ALL         
SELECT '1979 Ford LTD','','FORD','1979','','' UNION ALL         
SELECT '1979 Ford LTD','','FORD','1979','',''

declare @strsql varchar(2500)
  set @strsql = 'select '
  set @strsql += 
  (select case when  (select COUNT(*) from #T where BaseVehicle = '') 
  <> (select count(*) from #T ) then 'BaseVehicle, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #T  where SubModel = '') 
  <> (select count(*) from #T ) then 'SubModel, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #T  where Make = '') 
  <> (select count(*) from #T ) then 'Make, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #T  where Years = '') 
  <> (select count(*) from #T ) then 'Years, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #T  where FromYear = '') 
  <> (select count(*) from #T ) then 'FromYear, ' else '' end)
  set @strsql += 
  (select case when  (select COUNT(*) from #T  where ToYear = '') 
  <> (select count(*) from #T ) then 'ToYear, ' else '' end)
  set @strsql = LEFT(@strsql,len(@strsql) -1)
  set @strsql += ' from #T'

  exec (@strsql)

结果

BaseVehicle     Make    Years   ToYear
1979 Ford LTD   FORD    1979    
1979 Ford LTD   FORD    1979    2005
1979 Ford LTD   FORD    1979    
1979 Ford LTD   FORD    1979    

如果表在 INFORMATION_SCHEMA 中，您甚至可以动态地进行查询。