Hide Column With Null Values using SQL server


I don’t think this requirement should be implemented in SQL end.By using dynamic SQL with UNIPIVOT to get the result:


  
DECLARE @colsCast NVARCHAR(2000)
, @cols NVARCHAR(2000)
, @sql1 NVARCHAR(4000)
, @sql2 NVARCHAR(4000)
, @sql3 NVARCHAR(4000)
, @Schema_Name NVARCHAR(4000) ='dbo'
, @TABLE_NAME NVARCHAR(4000) ='Source'
 

SELECT @colsCast = COALESCE(@colsCast + ', ', '') + 'CAST('+ Quotename(column_Name)+' AS NVARCHAR(4000)) AS '+ Quotename(column_Name)
, @cols = COALESCE(@cols + ', ', '') + Quotename(column_Name)  
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME ANd Table_Schema=@Schema_Name

--print @cols 

SET @sql1='IF OBJECT_ID(N''dbo.stagingTable'',N''U'') IS NOT NULL
Begin
DROP Table dbo.stagingTable
END
Begin
SELECT  * INTO stagingTable
FROM (SELECT '+ @colsCast+ ' FROM  '+ @Schema_Name+'.'+ Quotename(@TABLE_NAME) + ') src
UNPIVOT (val For col IN ( '+ @cols+ ')) unpvt  
END'
EXEC sp_executesql @sql1


SET  @sql2 = (SELECT distinct  stuff((select ',' + m1.col
from (select distinct col as col from dbo.stagingTable) m1 
 for XML PATH('')),1,1,'') 
from( select distinct col as col from dbo.stagingTable) m Group By col )
 
 
 --print @sql2

 Set @sql3=N'SELECT '+@sql2+ ' FROM  '+ @Schema_Name+'.'+ @TABLE_NAME

 EXEC sp_executesql @sql3

--http://forums.asp.net/p/1931738/5501150.aspx?p=True&t=635139931216804858&pagenum=1

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s