Select only columns that have data in them?

Question

question

ksand asked Oct 15, '13

Select only columns that have data in them?

How to select only columns that are not empty or null? select (only columns that have data) from table where column 1 ='1' table: ![alt text][1] Result: ![alt text][2] [1]: /storage/temp/1118-column.jpg [2]: /storage/temp/1119-result.jpg

sql-server-2008

column.jpg (14.7 KiB)

result.jpg (13.0 KiB)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ commented · Oct 15, 2013 at 03:48 PM

Are you only after a single row? No need to take this: column1 column2 column3 column4 column5 1 2 3 4 1 2 3 1 2 3 and turn it into this: column2 column4 2 3 1 2 1 3

0 ·

Answer 1 · 2013-10-15T15:22:57Z

KenJ answered Oct 15, '13

You'll have to do something dynamic. This might be a good start for a single row: SELECT 1 AS Column1, 2 AS Column2, 3 AS Column3, '' AS Column4, 4 AS Column5, 5 AS Column6, NULL AS Column7 INTO #sourcetable DECLARE @sql VARCHAR(512); SELECT @sql = 'SELECT ' + SUBSTRING( (SELECT TOP 1 CASE WHEN LEN(Column1) >0 THEN ',Column1' ELSE '' END + CASE WHEN LEN(Column2) >0 THEN ',Column2' ELSE '' END + CASE WHEN LEN(Column3) >0 THEN ',Column3' ELSE '' END + CASE WHEN LEN(Column4) >0 THEN ',Column4' ELSE '' END + CASE WHEN LEN(Column5) >0 THEN ',Column5' ELSE '' END + CASE WHEN LEN(Column6) >0 THEN ',Column6' ELSE '' END + CASE WHEN LEN(Column7) >0 THEN ',Column7' ELSE '' END FROM #sourcetable) , 2, 1000) + ' FROM #sourcetable' EXEC( @sql) DROP TABLE #sourcetable

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question