SQL Puzzle | The Pivot Puzzle – VII | Improving my SQL BI Skills

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	4	5
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

3 thoughts on “SQL Puzzle | The Pivot Puzzle – VII”

Sai Vinutna said:

November 20, 2017 at 4:36 PM

select id,coalesce([0],’ ‘)as [0],coalesce([1],’ ‘)as [1],coalesce([2],’ ‘) as [2],coalesce([3],’ ‘) as [3],coalesce([4],’ ‘) as [4],coalesce([5],’ ‘) as [5],coalesce([6],’ ‘) as [6] from Transform
pivot
(
max(cau)
for times in ([0],[1],[2],[3],[4],[5],[6])
)a

LikeLike

Pingback: SQL Puzzle | Dynamic Pivot Puzzle | Improving my SQL BI Skills
revank219 said:

July 28, 2021 at 6:42 PM

with cte as (
select * from Transform
)
select id,isnull([0],”)as ‘0’,isnull([1],”)as ‘1’,isnull([2],”)as ‘2’,isnull([3],”)as ‘3’,isnull([4],”)as ‘4’,
isnull([5],”)as ‘5’ from cte
pivot (
min(cau) for Times in ([0],[1],[2],[3],[4],[5])
)as a

LikeLiked by 1 person

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28

Share this

Related

3 thoughts on “SQL Puzzle | The Pivot Puzzle – VII”

Leave a comment Cancel reply