Pivot help - 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

SQL Server 2012 Forums

Transact-SQL (2012)

Pivot help

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2013-03-13 : 13:04:37

Hi,

I feel a bit of an idiot not being able to work out this simple problem, but the documentation seems a little incomprehensible.

I have this data:


declare @pivottest table (strUserId nvarchar(20), strProductId nvarchar(20) )

insert into @pivottest values ( 'A12345', 'product1' )
insert into @pivottest values ( '12345', 'product1' )
insert into @pivottest values ( 'A5678', 'product2' )
insert into @pivottest values ( '5678', 'product2' )

Which results in


strUserId            strProductId
-------------------- --------------------
A12345               product1
12345                product1
A5678                product2
5678                 product2

But what I actually want is this:


product1             product2
-------------------- --------------------
A12345               A5678
12345                5678

My feeble attempt to produce this result looks like this:


select * from
( select strUserId, strProductId from @pivottest ) sq
pivot
(
max(strUserId)
	for strProductId in ([product1],[product2])	
) piv

But that just gives me one row. How can I get them all?

Cheers,
Matt

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-13 : 14:18:07

Yes, PIVOT behavior is funky. This may not be the easiest way but it works (thanks for the DDL/DML!)


select product1, product2 from
( select strUserId
       , strProductId
       , rn = row_number() over (partition by strProductID order by strUserId) from @pivottest 
       ) sq
pivot
(
max(strUserId)
	for strProductId in ([product1],[product2])	
) piv

Be One with the Optimizer
TG

mattt
Posting Yak Master

194 Posts

Posted - 2013-03-14 : 05:58:29

Great, thanks.

So if I understand correctly, you're adding a rownumber to ensure that each row has a unique identifier? If so, then the actual table has an Id I can use - will have a play.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources