very simple pivot table - 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

very simple pivot table

Author

Topic

paulholzweber
Starting Member

4 Posts

Posted - 2009-01-20 : 05:46:09

Hi,

I'm trying to transform

col1 col2 col3
A1 x 1
A1 y 2
A1 z 3
A2 x 4
A2 y 5
A2 z 6
A3 x 7
A3 y 8
A3 z 9

into

x y z
A1 1 2 3
A2 4 5 6
A3 7 8 9

but I'm quite struggeling with the pivot function. Is this a case for pivot at all? Looks quite simple, but I just can't make it.

Can anybody help me please?
Thanks!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 05:56:22

SELECT col1, [x] AS x ,[y] AS y ,[z] AS z
FROM
@tab P
PIVOT (max(col3) FOR col2 IN (x ,y , z ))
AS PVT

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-20 : 05:58:10

U can also use cross tab for this

Jai Krishna

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 05:58:24

select col1,max(case when col2 = 'x' then col3 end ) as x,
max(case when col2 = 'y' then col3 end ) as y,
max(case when col2 = 'z' then col3 end ) as z
from @tab
group by col1

paulholzweber
Starting Member

4 Posts

Posted - 2009-01-20 : 07:16:15

Hi!

Thanks, that works perfectly!
But is there also a way to avoid the listing of the new columns, for the case I don not know them in advance?

quote:
Originally posted by bklr

SELECT col1, [x] AS x ,[y] AS y ,[z] AS z
FROM
@tab P
PIVOT (max(col3) FOR col2 IN (x ,y , z ))
AS PVT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-20 : 07:43:31

welcome

if there are any new columns u have to add in pivot query as x,y,z add new columns

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-20 : 08:08:27

quote:
Originally posted by paulholzweber

Hi!

Thanks, that works perfectly!
But is there also a way to avoid the listing of the new columns, for the case I don not know them in advance?

quote:
Originally posted by bklr

SELECT col1, [x] AS x ,[y] AS y ,[z] AS z
FROM
@tab P
PIVOT (max(col3) FOR col2 IN (x ,y , z ))
AS PVT

Then Search for Dynamic Pivot or dynamic Crosstab in here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 08:26:23

quote:
Originally posted by paulholzweber

Hi!

Thanks, that works perfectly!
But is there also a way to avoid the listing of the new columns, for the case I don not know them in advance?

quote:
Originally posted by bklr

SELECT col1, [x] AS x ,[y] AS y ,[z] AS z
FROM
@tab P
PIVOT (max(col3) FOR col2 IN (x ,y , z ))
AS PVT

see this
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources