Tags

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


SQL Puzzle | The Pivot Puzzle – VII

In this puzzle you have to write a T-SQL which will transform the data into column format. If any column is missed then we have to generate that column.

Please check out the sample input values and sample expected output below.

Sample Input

ID Cau Times
939861018 E8889 0
939861018 E8889 1
939861954 E8889 0
939861988 E8788 5
939861772 E8889 0
939861772 E8889 4

Expected Output

Id 0 1 2 3 4 5 6
939861018 E8889 E8889          
939861772 E8889       E8889    
939861954 E8889            
939861988           E8788

Script

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

--

CREATE TABLE Transform
(
	 ID VARCHAR(100)
	,Cau VARCHAR(100)
	,Times INT
)
GO

INSERT INTO Transform VALUES
('939861018','E8889',0),
('939861018','E8889',1),
('939861954','E8889',0),
('939861988','E8788',5),
('939861772','E8889',0),
('939861772','E8889',4)
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 SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2
)
,CTE AS
(
	SELECT Number - 1 Number FROM Series
)
,CTE1 AS
(
	SELECT t.Id,t.Cau,ISNULL(t.Times,s.Number) Times FROM CTE s
	LEFT JOIN Transform t
	ON S.Number = t.Times
	WHERE Number <= ( (SELECT MAX(Times) FROM Transform t ) ) 
	AND Number >= 0 
)
SELECT Id,ISNULL([0],'') [0],ISNULL([1],'') [1],ISNULL([2],'') [2],
ISNULL([3],'') [3],
ISNULL([4],'') [4],
ISNULL([5],'') [5],
ISNULL([6],'') [6]
FROM CTE1
PIVOT 
(
    MIN(Cau)
    FOR [Times] IN ([0],[1],[2],[3],[4],[5],[6]) 
)t 
WHERE Id IS NOT NULL

--

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