Tags

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


SQL Puzzle | The ListAggs Puzzle

In this puzzle you have to add val to all its previous val, just like running total.For more details please check the sample input and expected output.

Sample Input

Id Vals
1 P
2 Q
3
4 D
5 E
6 F

Expected Output

Id sname
1 P
2 P Q
3 P Q
4 P Q D
5 P Q D E
6 P Q D E F

Script

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

--

 
CREATE TABLE ListAggs
(
	 Id INT
	,Vals VARCHAR(10)
)
GO

INSERT INTO ListAggs
SELECT 1 Id, 'P' Vals  UNION ALL
SELECT 2 Id, 'Q' Vals  UNION ALL
SELECT 3 Id, ''  Vals   UNION ALL
SELECT 4 Id, 'D' Vals  UNION ALL
SELECT 5 Id, 'E' Vals  UNION ALL
SELECT 6 Id, 'F' Vals 
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

--

/*old Solution using XML PATH and NVARCHAR(MAX) */
SELECT Id
,((SELECT Vals+' ' 
	 from ListAggs t2 where t2.Id <= t1.Id
	 ORDER BY Id
	FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))
	 sname
FROM ListAggs t1
 
--

Output-1

--

/*------------------------
SELECT Id
,((SELECT Vals+' ' 
	 from ListAggs t2 where t2.Id <= t1.Id
	 ORDER BY Id
	FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))
	 sname
FROM ListAggs t1
------------------------*/
Id          sname
----------- ---------------------------------------------------
1           P 
2           P Q 
3           P Q  
4           P Q  D 
5           P Q  D E 
6           P Q  D E F 

(6 row(s) affected)



--

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