Tags

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


SQL Puzzle | Convert Multi-Colums to Rows(UNPIVOT Alternative) | Advanced SQL

In this puzzle you have to convert the columns into rows without using the UNVPIVOT command. For more details please see the sample input and expected output.

Sample Input

ProjectName Dev Start Date Dev End Date Testing Plan Date Testing End Date
P1 2017-01-01 00:00:00.000 2017-06-01 00:00:00.000 2017-06-04 00:00:00.000 2017-08-24 00:00:00.000
P2 2016-06-01 00:00:00.000 2017-01-01 00:00:00.000 2017-01-10 00:00:00.000 2017-02-26 00:00:00.000
P3 2014-03-01 00:00:00.000 2015-03-25 00:00:00.000 2015-04-01 00:00:00.000 2015-07-20 00:00:00.000
P4 2012-02-01 00:00:00.000 2012-10-29 00:00:00.000 2012-11-10 00:00:00.000 2013-02-05 00:00:00.000

Expected Output

ProjectName WorkType StDt EnDt
P1 Developement 2017-01-01 00:00:00.000 2017-06-01 00:00:00.000
P2 Developement 2016-06-01 00:00:00.000 2017-01-01 00:00:00.000
P3 Developement 2014-03-01 00:00:00.000 2015-03-25 00:00:00.000
P4 Developement 2012-02-01 00:00:00.000 2012-10-29 00:00:00.000
P1 Testing 2017-06-04 00:00:00.000 2017-08-24 00:00:00.000
P2 Testing 2017-01-10 00:00:00.000 2017-02-26 00:00:00.000
P3 Testing 2015-04-01 00:00:00.000 2015-07-20 00:00:00.000
P4 Testing 2012-11-10 00:00:00.000 2013-02-05 00:00:00.000

Script – DDL and INSERT Sample Data

--

CREATE TABLE GetSequenceGaps 
(
	 Id INT
	,Vals int
)
GO

INSERT INTO GetSequenceGaps VALUES
 (1,11)
,(2,13)
,(3,12)
,(4,13)
,(5,11)
,(6,12)
,(7,13)
,(8,13)
,(9,11)
,(10,13)
,(11,13)
,(12,13)
,(13,12)
,(14,13)
,(15,11)
GO

SELECT * FROM GetSequenceGaps
GO

--

SOLUTION – 1

--

SELECT ProjectName,z.* 
FROM MyProject
CROSS APPLY
(VALUES 
    (
		'Developement' , [Dev Start Date],  [Dev End Date]
	),
    (
		'Testing' , [Testing Plan Date] ,  [Testing End Date]
	)
)z
(
	WorkType,StDt, EnDt
)

--

Output – 1

--

ProjectName          WorkType     StDt                    EnDt
-------------------- ------------ ----------------------- -----------------------
P1                   Developement 2017-01-01 00:00:00.000 2017-06-01 00:00:00.000
P1                   Testing      2017-06-04 00:00:00.000 2017-08-24 00:00:00.000
P2                   Developement 2016-06-01 00:00:00.000 2017-01-01 00:00:00.000
P2                   Testing      2017-01-10 00:00:00.000 2017-02-26 00:00:00.000
P3                   Developement 2014-03-01 00:00:00.000 2015-03-25 00:00:00.000
P3                   Testing      2015-04-01 00:00:00.000 2015-07-20 00:00:00.000
P4                   Developement 2012-02-01 00:00:00.000 2012-10-29 00:00:00.000
P4                   Testing      2012-11-10 00:00:00.000 2013-02-05 00:00:00.000

(8 rows affected)

--

SOLUTION – 2

--

SELECT ProjectName , 'Developement' WorkType , [Dev Start Date] StDt,  [Dev End Date] EnDt
FROM MyProject 
UNION ALL
SELECT ProjectName , 'Testing' , [Testing Plan Date] ,  [Testing End Date]
FROM MyProject
ORDER BY ProjectName, [Dev Start Date]

--

Output – 2

--

ProjectName          WorkType     StDt                    EnDt
-------------------- ------------ ----------------------- -----------------------
P1                   Developement 2017-01-01 00:00:00.000 2017-06-01 00:00:00.000
P1                   Testing      2017-06-04 00:00:00.000 2017-08-24 00:00:00.000
P2                   Developement 2016-06-01 00:00:00.000 2017-01-01 00:00:00.000
P2                   Testing      2017-01-10 00:00:00.000 2017-02-26 00:00:00.000
P3                   Developement 2014-03-01 00:00:00.000 2015-03-25 00:00:00.000
P3                   Testing      2015-04-01 00:00:00.000 2015-07-20 00:00:00.000
P4                   Developement 2012-02-01 00:00:00.000 2012-10-29 00:00:00.000
P4                   Testing      2012-11-10 00:00:00.000 2013-02-05 00:00:00.000

(8 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com