Transposing a table before Joining

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

Transposing a table before Joining

Author

Topic

Sfosri
Starting Member

1 Post

Posted - 2011-03-02 : 17:47:21

I've searched through this and various forums looking for a way to transpose i.e switch rows into columns while keeping the unique ID column. The example below should illustrate what I wish to do:

Current Table:

ID IDENT VALUE
1 JOBNAME ABCDEFGH
1 JOBNUM 123456
1 USERID RANDY
2 JOBNAME BBBCCCDD
2 JOBNUM 123423
2 USERID SRINI

Desired Table

ID JOBNAME JOBNUM USERID
1 ABCDEFGH 123456 RANDY
2 BBBCCCDD 123423 SRINI

As you can see the fields JOBNAME JOBNUM and USERID are fixed for this kind of table. It was suggested on many forum's to use Pivot, but you have to use an aggregate function since thats the point of pivot tables, but it does not seem feasible here since I do not see any column to aggregate on...unless the key ID field can be used.

Any suggestions would be greatly appreciated, I am a newbie at SQL Server and am trying to use SQL Server Reporting Services on SQL Server 2008 and I need to create such a table.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-02 : 18:12:26

You could do it with CASE statements

SELECT t.ID,[JobName] = MAX(CASE WHEN t1.IDENT = 'JOBNAME' THEN t1.Value END)
,[JobNum] = MAX(CASE WHEN t1.IDENT = 'JOBNUM' THEN t1.value END)
,[USERID] = MAX(CASE WHEN t1.IDENT = 'USERID' THEN t1.value END)
FROM yourTable t
GROUP BY t1.ID

The MAX is used since without it you will get a lot of null values

Jim

Everyday I learn something that somebody else already knew

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-03-02 : 18:12:29

Pivot on max (or min) ID.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-02 : 18:13:19

Use MAX or MIN for your aggregate function in PIVOT. They work on most data types, definitely varchar anyway. If you only have one value for each ID-IDENT combination then it'll work just like you want.

edit: yeah, what they said

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources