Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The NULL Column Puzzle

This question was asked to me by a close friend, Parveen. He has been a good friend and very very good MSBI & SQL Architect. In this case you have to get column names where all values present in that column are NULL.

Please check out the sample input values and sample expected output below.

Sample Input

ID1 ID2 ID3
1 2 NULL
2 3 NULL
1 8 NULL
6 2 NULL
6 3 NULL

Expected Output

ColumnWithNullValuesOnly
ID3

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE testN1
(
	 ID1 INT 
	,ID2 INT
	,ID3 INT
)
GO

INSERT INTO testN1 VALUES
(1,2,NULL),
(2,3,NULL),
(1,8,NULL),
(6,2,NULL),
(6,3,NULL)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION – 1 | Using Informational schema table – Dynamic COLUMNS


--

DECLARE @TableName AS VARCHAR(50) = 'testN1'
DECLARE @Schema AS VARCHAR(50) = 'dbo'
DECLARE @Cols AS VARCHAR(MAX) = ''

SELECT @Cols = @Cols + v FROM 
(
	SELECT CASE WHEN rnk < cnt THEN CONCAT(v,',') ELSE v END v FROM 
	(
		SELECT   SUBSTRING(REPLACE(v,'Parveen', ''''+COLUMN_NAME+'''') ,1,LEN(REPLACE(v,'Pawan', ''''+COLUMN_NAME+''''))) v 
				,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
				,COUNT(*) OVER() cnt
		FROM 
		(
				SELECT REPLACE(v,'Pawan',x.COLUMN_NAME) v , COLUMN_NAME FROM 
				(			
					SELECT ' MAX( CASE WHEN Pawan IS NULL THEN Parveen ELSE '''' END ) ' v
				)t
				CROSS APPLY
				(
						SELECT 
							COLUMN_NAME
						FROM  
							INFORMATION_SCHEMA.COLUMNS xx
						WHERE 
								TABLE_NAME = @TableName
							AND TABLE_SCHEMA = @Schema
				)x
		)j
	)h
)g

SET @cols = CONCAT (' SELECT CONCAT ( ' , @cols , ' ) ColumnWithNullValuesOnly'  , ' FROM ' , @Schema , '.' , @TableName )
EXEC (@cols)


--

SOLUTION – 2 | Using HardCoded column names of table


--

SELECT  CONCAT
		(  
			 MAX(CASE WHEN ID1 IS NULL THEN 'Id1' ELSE ''  END ) 
			,MAX(CASE WHEN ID2 IS NULL THEN 'Id2' ELSE ''  END ) 
			,MAX(CASE WHEN ID3 IS NULL THEN 'Id3' ELSE ''  END ) 

		) ColumnswithAllNullValues
FROM testN1

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com