Cybercrux

Everything is achievable through technology

Row as Column

Sample code


DECLARE @Temp TABLE(Capacity INT,CDate DATE,Name NVARCHAR(100))
INSERT INTO @Temp VALUES (1,'4/14/2014','M24')
INSERT INTO @Temp VALUES (1,'4/15/2014','M22')
INSERT INTO @Temp VALUES (1,'4/14/2014','M24')
INSERT INTO @Temp VALUES (1,'4/15/2014',NULL)
INSERT INTO @Temp VALUES (2,'4/14/2014','F67')
INSERT INTO @Temp VALUES (2,'4/15/2014','F31')
INSERT INTO @Temp VALUES (3,'4/14/2014','M53')
SELECT * FROM @Temp
SELECT Capacity, [2014-04-14], [2014-04-15]
FROM
(
SELECT Capacity,
CDate,
Name,
row_number() over(partition by capacity, cdate order by capacity) seq
FROM @Temp
) d
PIVOT
(
MAX(name)
FOR CDate IN ([2014-04-14], [2014-04-15])
) piv
ORDER BY Capacity

Reference for PIVOT and UNPIVOT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s