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
Improving my SQL BI Skills 