Post reply

Rows to Columns

  • 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:-):-)

  • 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!

  • 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]

  • January 11, 2011 at 7:33 am

    #1271878

    you can view this post

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

  • 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;

  • January 12, 2011 at 2:31 am

    #1272236

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply