Traverse rows to columns

[bangalibhai]

Hi,

I am trying to display the rows as columns. I have seen a similar posting but can't seem to find it. Initially I have the below table.....

CUSTOMER CUST # ITEMS QTY
====================================
John Smith 1234 Item1 2
John Smith 1234 Item2 1
John Smith 1234 Item3 3
Joe Blow 1111 Item4 1
Joe Blow 1111 Item2 1
John Doe 2222 Item2 1
John Doe 2222 Item3 1
John Doe 2222 Item4 2
John Q 3333 Item1 1

I want to display as below....

CUSTOMER CUST # Item1 Item2 Item3 Item4
======================================================
John Smith 1234 2 1 3 0
Joe Blow 1111 0 1 0 1
John Doe 2222 0 1 1 2
John Q 3333 1 0 0 0

Your help is greatly appreaciated.

Thanks.

 

[maswien]

select customer, max(cust),
sum(case when items = 'item1' then qty else 0 end) as 'item1',
sum(case when items = 'item2' then qty else 0 end) as 'item2',
sum(case when items = 'item3' then qty else 0 end) as 'item3',
sum(case when items = 'item4' then qty else 0 end) as 'item4'
from table7
group by customer

 

[bangalibhai]

Thanks Mija, actually there are 50 to 80 items. I actually thought of doing it this way. But I was looking for a more efficient way so that I do not have to list all the items.