How do I display my rows as columns

[susanh]

Hi Again,

I am trying to create an inventory report that displays different vendors and their price by item numbers.

So part of my report creation is to grab the data and dump it into a temp table like the following

DROP TABLE temp_vbelts_table5

SELECT POR_VendorPriceLevel.itemnumber, POR_VendorPriceLevel.VendorNumber,
POR_VendorPriceLevel.DiscountMarkup1 INTO temp_vbelts_table5

FROM temp_vbelts_table1, POR_VendorPriceLevel

WHERE temp_vbelts_table1.itemnumber = POR_VendorPriceLevel.ItemNumber

ORDER BY temp_vbelts_table1.itemnumber

so my data is like the following:

Vendor1 25.50
Vendor2 45.00
Vendor3 75.00

What would like to learn how to do is if possible is output the data so each vendor is it's own column with the according price for the item? Is this possible?

Sue

 

[susanh]

I could just write another step where I add the columns to the main table and then just write a litte update query right?

 

[sagn]

I believe a transpose pivot
of the table would do the trick

select
'Vendor1' as VENDOR, 25.50 as X
into vend
union
select
'Vendor2', 45.00
union
select
'Vendor3', 75.00

select min(case when Vendor = 'Vendor1' then X end) as Vendor1,
min(case when Vendor = 'Vendor2' then X end) as Vendor2,
min(case when Vendor = 'Vendor3' then X end) as Vendor3
from vend

More generally:
drop table #tb
create table #tb (record_id int, obj_id char(4), reference char(10))

insert #tb values(1, 'Btn1', 'forward')
insert #tb values(1, 'Btn2', 'backward')

select * from #tb
-- this is a test edit
select record_id, min(case when obj_id = 'btn1' then reference end) as btn1,
min(case when obj_id = 'btn2' then reference end) as btn2
from #tb
group by record_id

 

[susanh]

Thank you sagn!

I ended up just breaking it down into a bunch of little updates.

I know that it may not be the best way to do it and SQL can do so much more powerful stuff, but it really breaks it down for me into a process that I can understand.

Thanks again
Sue