0

I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is what I'm searching for, and I'm new to pivot tables.

This is my example:

ItemID    VendorName    VendorPrice
122       HP            125.66
122       Apple         130.44
122       Microsoft     134.00
122       IBM           124.90

This is my expected result:

ItemID      HPPrice    ApplePrice  MicrosoftPrice   IBMPrice
122         125.66     130.44      134.00           124.90

How can I build the result using pivot tables?

good-to-know
  • 742
  • 3
  • 15
  • 32
  • Define "efficiently" and "lot of records". For hundreds of thousands of records, you can't use tables designed for transaction processing for reporting queries. The fastest option is to load the data in the form you want to reporting tables or create a data mart. This way you can do simple row lookups even if you have several millions of records. For fewer entries - check whether you *do* have a problem. Most likely you are missing indexes on the proper columns – Panagiotis Kanavos May 18 '15 at 11:03
  • Have you actually tested pivoting the data to prove what you state in the question? If not, I suggest you do that first before looking for alternatives. The question is too broad as it stands. – Tanner May 18 '15 at 11:05
  • possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – jpw May 18 '15 at 11:15
  • @jpw, that question is not for pivot tables but this's for pivot tables. – good-to-know May 18 '15 at 12:31
  • @ProgrammingNewbie It's a perfect match. Just look at the top answer. Apart from using different column names it's exactly identical to the answer you accepted here. Pivot is just a word for converting rows to columns. – jpw May 18 '15 at 12:38

1 Answers1

0
declare  @table  table (Itemid Int,Vendorname varchar(10),VendorPrice DECIMAL(18,2))
insert into @table (Itemid,Vendorname,VendorPrice)
values (122,'HP',125.66),
(122,'Apple',130.44),
(122,'microsoft',134.00),
(122,'IBM',124.90)

Select Itemid,[HP] As HPPrice ,
[Apple] As ApplePrice,
[microsoft] As microsoftPrice,
[IBM] As IBMPrice from (
select Itemid,Vendorname,VendorPrice from @table)A
PIVOT(MAX(VendorPrice) FOR Vendorname IN ([HP],[Apple],[microsoft],[IBM]))P
mohan111
  • 8,633
  • 4
  • 28
  • 55