Pivot problem - 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 2008 Forums

Transact-SQL (2008)

Pivot problem

Author

Topic

IceDread
Yak Posting Veteran

66 Posts

Posted - 2010-11-22 : 08:02:31

Hi!

I've searched around for a while now and can not find the answer to my need and I really hope someone here can help me with it.

I have two tables of interest.

Table Customer with CustomerId and PersonalId
Table Product with ProductId and CustomerId
There are 100 000 + customers and 100 000 + products.
Each customer has 1-20 products.

What I want is a result something like this as a result:

PersonalId, product1, product2, product3...
1970xxxx,1,2,3
1976xxxx,4,5,6,7

Hopefully you get the picture of what I'm after here.

I'd really appreciate your help!

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 08:15:10

;with cte as
(
select c.customerid, p.productid, seq = row_number() over (partition by c.customerid order by p.productid)
from Customer c
join Product p
on c.customerid = p.customerid
)
select customerid,
product1 = max(case when seq=1 then productid end,
product2 = max(case when seq=2 then productid end,
...
from cte
group by customerid

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

IceDread
Yak Posting Veteran

66 Posts

Posted - 2010-11-22 : 08:31:54

Tanks nigelrivett, I just came up with the idea to use row_number windowed clause as well. Checked in to see if someone hopefully had done a pivot. I'd prefer to use ROW_NUMBER() over (partition by c.customerid order by p.productid) as number, together with a pivot clause to have it dynamically if that is possible, I'm very green when it comes to pivot, however I can probably figure that out sooner or later. I got stuck on the idea of pivot and over clause is really handy here to create the pivot columns I think.

Thanks for taking the time to help me out thou!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 08:48:43

;with cte as
(
select c.customerid, p.productid, seq = row_number() over (partition by c.customerid order by p.productid)
from Customer c
join Product p
on c.customerid = p.customerid
)
select customerid, [1] product1, [2] product2, ...
from
(select customerid, productid, seq from cte) a
pivot
(
max(productid) for seq in ([1],[2],...)
) as pvt

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

IceDread
Yak Posting Veteran

66 Posts

Posted - 2010-11-22 : 09:08:35

Very very nice!
It runs perfectly and I have with what you've done been given a better understanding for pivot as well.

Thanks for you're help and time, I really appreciate it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources