Tags

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


SQL PUZZLE | Retrieve only columns which having at least one not null value | SQL Interview Question

In this puzzle you have to select columns where you have NON NULL values only. If all the values in the column are NULL then do not SELECT that column

For more details please refer sample input and expected output.

Sample Input

Id Val1 Val2
1 12 NULL
2 1012 NULL
3 23 NULL
4 92 NULL
5 31 NULL
6 87 NULL
7 75 NULL
8 10 NULL

Expected output

Id Val1
1 12
2 1012
3 23
4 92
5 31
6 87
7 75
8 10

Script – DDL and INSERT sample data

--

CREATE TABLE TheZeroPUZZLE
(
	 ID INT
	,Dat VARCHAR(10)
	,Val INT
)
GO

INSERT INTO TheZeroPUZZLE VALUES
(1,'Pawan', 0  ),
(2,'Pawan', 1  ),
(3,'Pawan', 2  ),
(4,'Jonny', 1 ),
(5,'Jonny', 2 ),
(6,'Jonny', 3 ),
(7,'Avtaar', 0 ),
(8,'Avtaar', 1 )
GO

SELECT * FROM TheZeroPUZZLE
GO

--

SOLUTION 1 | Dynamic STRING

--

IF OBJECT_ID('tempdb..#s') IS NOT NULL
    DROP TABLE #s

CREATE TABLE #s ( a VARCHAR(200))

DECLARE @TableName AS VARCHAR(50) = 'NotNULLColumns'
DECLARE @Schema AS VARCHAR(50) = 'dbo'
DECLARE @Cols AS NVARCHAR(MAX) = ''
DECLARE @GetDATA 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 NOT 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  , ' ) '  , ' FROM ' , @Schema , '.' , @TableName )

INSERT INTO #s 
EXEC (@Cols)

SELECT @GetDATA = SUBSTRING(a, 1 , LEN(a)-1) FROM #s
SET @cols = CONCAT ( ' SELECT ' , @Getdata , ' FROM ' , @Schema , '.' , @TableName )  
EXEC (@Cols)


--

OUTPUT 1

--


Id          Val1
----------- -----------
1           12
2           1012
3           23
4           92
5           31
6           87
7           75
8           10

(8 rows affected)


--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com