Transpose data in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transpose data in SQL

Author

Topic

anirudhapande
Starting Member

2 Posts

Posted - 2013-09-11 : 14:49:54

I need help with transposing the following data:

COL1 COL2 COL3
ABCD A1 1
ABCD A2 2
ABCD A3 3
ABCD A4 4
EFGH A1 5
EFGH A2 0
EFGH A3 6
EFGH A4 7
IJKL A1 8
IJKL A2 9
IJKL A4 10

The output should look like this:

COL4 ABCD EFGH IJKL
A1 1 5 8
A2 2 0 9
A3 3 6 NULL
A4 4 7 10

The data from COL1 (ABCD,EFGH,IJKL) is dynamic and hence, its cumbersome to create manual workarounds.

I have been trying to play with the PIVOT function and other cross-tab examples online but nothing seems to be working.

I am new to this forum so i apologise if this has been discussed before.

Thank you for your help in advance.

erikhaselhofer
Starting Member

30 Posts

Posted - 2013-09-11 : 16:25:17

Here's one way to do it.

SELECT col2 AS 'col4',
MAX(IIF(col1 = 'ABCD',col3,NULL)) AS ABCD,
MAX(IIF(col1 = 'EFGH',col3,NULL)) AS EFGH,
MAX(IIF(col1 = 'IJKL',col3,NULL)) AS IJKL
FROM junk
GROUP BY col2

col4	ABCD	EFGH	IJKL
A1	1	5	8
A2	2	0	9
A3	3	6	NULL
A4	4	7	10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-12 : 15:58:27

IIF works only from 2012. so if you're on earlier version you should be using CASE WHEN instead of IIF


SELECT col2 AS 'col4',
MAX(CASE WHEN col1 = 'ABCD' THEN col3 END) AS ABCD,
MAX(CASE WHEN col1 = 'EFGH' THEN col3 END) AS EFGH,
MAX(CASE WHEN col1 = 'IJKL' THEN col3 END) AS IJKL
FROM junk
GROUP BY col2

or you could simply use PIVOT


SELECT *
FROM (SELECT col1,col2,col3 FROM junk)j
PIVOT (MAX(col3) FOR col1 IN ([ABCD],[EFGH],[IJKL]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

anirudhapande
Starting Member

2 Posts

Posted - 2013-09-13 : 11:33:19

@erikhaselhofer - Thanks for providing a solution so quickly :) Unfortunately, I am using SQL server 2008 so IIF was not a built in function. I did do some reasearch and as per visakh16's soultion, was playing with the case statement to replace IIF.

@visakh16 - Thank you for the solution examples. I was looking into CASE but the PIVOT seems simpler and i did not know that.

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-15 : 03:45:56

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources