SQL Puzzle | Concatenation of columns with comma and add blank if missing data

In this puzzle you have to concat the columns with comma and add blank if missing data. For more details please check the sample input and expected output.

Sample Input

Vals Data
a 1
a 5
c 9
c 7
b 6
b 4
b 6
d 1

Expected Output

Vals FinalCombination
a 1,5,””
b 6,4,6
c 9,7,””
d 1,””,””

Script

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

--

CREATE TABLE AddBlankToCountDiffer
(
	 Vals VARCHAR(100)
	,[Data] VARCHAR(100)
)
GO

INSERT INTO AddBlankToCountDiffer VALUES
('a',1),
('a',5),
('c',9),
('c',7),
('b',6),
('b',4),
('b',6),
('d',1)
GO

SELECT * FROM AddBlankToCountDiffer
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

--

;WITH CTE1 AS
(
    SELECT  Vals ,  MAX(COUNT(*)) OVER() - COUNT(*) c  FROM AddBlankToCountDiffer
    GROUP BY Vals
)
,CTE2 AS
(
    SELECT Vals , [Data]  
    FROM AddBlankToCountDiffer 
    UNION ALL
    SELECT c.Vals , '""' [Data] FROM CTE1 c
    INNER JOIN master..spt_values m  ON c.c > m.number 
    WHERE m.type = 'p' AND c.c > 0
)
SELECT c.Vals , 
        STUFF((
        SELECT ',' + [Data]
        FROM CTE2 c1      
        WHERE c1.Vals = c.Vals       
        ORDER BY c1.Vals
        FOR XML PATH('')
        ),1,1,'') FinalCombination
FROM CTE2 c 
GROUP BY c.Vals

--

Output-1

--                            

Vals                 FinalCombination
-------------------- ------------
a                    1,5,""
b                    6,4,6
c                    9,7,""
d                    1,"",""

(4 rows affected)

                                    
--

Excellent Solution by Vaibhav

--                            

SELECT
    T1.Vals
    , CONCAT( STUFF((
        SELECT ',' + [Data]
        FROM AddBlankToCountDiffer T       
        WHERE T.Vals = T1.Vals       
        ORDER BY T.Vals
        FOR XML PATH('')
    ),1,1,'')       , REPLICATE(',""',MAX(COUNT(*)) OVER () - COUNT(*)) ) FinalCombination
FROM AddBlankToCountDiffer T1    
GROUP BY T1.Vals
                                    
--

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

Advertisements