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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -