SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Transpose data in SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anirudhapande
Starting Member

USA
2 Posts

Posted - 09/11/2013 :  14:49:54  Show Profile  Reply with Quote
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 - 09/11/2013 :  16:25:17  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/12/2013 :  15:58:27  Show Profile  Reply with Quote
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
Go to Top of Page

anirudhapande
Starting Member

USA
2 Posts

Posted - 09/13/2013 :  11:33:19  Show Profile  Reply with Quote
@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!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/15/2013 :  03:45:56  Show Profile  Reply with Quote
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000