Rows to Columns

• pandukal21

  SSC Rookie

  Points: 27

  More actions

  January 11, 2011 at 6:18 am

  #233121

  I've a data in the following format....

  VehicleID CarName VendorName Quantity Price

  1 Honda Smith 20 25000

  1 Honda Ross 30 20000

  1 Honda Nick 40 10000

  1 Nissan Steve 25 24000

  1 Nissan Bruce 15 18000

  1 Nissan Sean 20 40000

  2 Honda Kate 30 90000

  2 Honda Ricky 40 20000

  2 Honda Shaun 10 15000

  I'd like to convert the above data into following format....

  VehicleID CarName Vendor1 Quantity1 Price1 Vendor2 Quantity2 Price2 Vendor3 Price3

  1 Honda Smith 20 25000 Ross 30 20000 Nick 10000

  1 Nissan Steve 25 24000 Bruce 15 18000 Sean 40000

  2 Honda Kate 30 90000 Ricky 40 20000 Shaun 15000

  Pls Note: 1. One VehicleID can have any number of CarName.

  2. There can be any number of VehicleID.

  3. But One CarName can have maximum upto only 3 Vendors.

  How can I achieve this???

  Kindly Help..!!!!

  Thanks in advance:-):-)

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 11, 2011 at 6:53 am

  #1271853

  your data in a format anyone can use in SSMS to look at the problem properly:

  SELECT '1' AS VehicleID,'Honda' AS CarName,'Smith' AS VendorName,'20' AS Quantity,'25000' AS Price UNION ALL

  SELECT '1','Honda','Ross','30','20000' UNION ALL

  SELECT '1','Honda','Nick','40','10000' UNION ALL

  SELECT '1','Nissan','Steve','25','24000' UNION ALL

  SELECT '1','Nissan','Bruce','15','18000' UNION ALL

  SELECT '1','Nissan','Sean','20','40000' UNION ALL

  SELECT '2','Honda','Kate','30','90000' UNION ALL

  SELECT '2','Honda','Ricky','40','20000' UNION ALL

  SELECT '2','Honda','Shaun','10','15000'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• LutzM

  SSC Guru

  Points: 107049

  More actions

  January 11, 2011 at 7:08 am

  #1271862

  It looks like a job for the CrossTab method.

  Please see the related link in my signature.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• roi.reuven

  SSCommitted

  Points: 1700

  More actions

  January 11, 2011 at 7:33 am

  #1271878

  you can view this post

  http://www.sqlservercentral.com/Forums/Topic1045762-391-1.aspx#bm1045818

• roi.reuven

  SSCommitted

  Points: 1700

  More actions

  January 11, 2011 at 7:42 am

  #1271882

  Here is an example of something that is doing the

  Reverse of what you are trying to do

  CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

  Emp3 int, Emp4 int, Emp5 int);

  GO

  INSERT INTO pvt VALUES (1,4,3,5,4,4);

  INSERT INTO pvt VALUES (2,4,1,5,5,5);

  INSERT INTO pvt VALUES (3,4,3,5,4,4);

  INSERT INTO pvt VALUES (4,4,2,5,5,4);

  INSERT INTO pvt VALUES (5,5,1,5,5,5);

  GO

  --Unpivot the table.

  SELECT VendorID, Employee, Orders

  FROM

  (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

  FROM pvt) p

  UNPIVOT

  (Orders FOR Employee IN

  (Emp1, Emp2, Emp3, Emp4, Emp5)

  )AS unpvt;

• pandukal21

  SSC Rookie

  Points: 27

  More actions

  January 12, 2011 at 2:31 am

  #1272236

  [font="Comic Sans MS"]Thanks for all your valuable suggestions ppl...cheers..!!!:-):-)[/font]