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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Pivot help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattt
Posting Yak Master

194 Posts

Posted - 03/13/2013 :  13:04:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 03/13/2013 :  14:18:07  Show Profile  Reply with Quote
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 - 03/14/2013 :  05:58:29  Show Profile  Reply with Quote
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
  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.06 seconds. Powered By: Snitz Forums 2000