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”.

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