www.webdeveloper.com
Results 1 to 2 of 2

Thread: How to write this SQL ?

  1. #1
    Join Date
    Aug 2012
    Posts
    1

    How to write this SQL ?

    SQL Problem --

    We have a table of orders.
    CREATE TABLE Orders (ordernumber INT, rownumber INT, Description VARCHAR(10))
    The rownumber is guaranteed to always between 1 and 5 and is always filled in consecutively.
    Write SQL that will return the rownumbers as column headers, with the description as the column

    values. Output similar to below:

    Order# Row1 Row2 Row3 Row4 Row5
    10 Desc1 Desc2
    20 Desc1 Desc2 desc3

    Thanks!

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    Code:
    SELECT 
      ordernumber `order#`, 
      ( SELECT max(`Description`) 
        FROM Orders ooooiiiii 
        WHERE ooooiiiii.ordernumber = Orders.ordernumber
          AND rownumber = 1) as `Row1`,
      ( SELECT max(`Description`) 
        FROM Orders ooooiiiii 
        WHERE ooooiiiii.ordernumber = Orders.ordernumber
          AND rownumber = 2) as `Row2`,
      ( SELECT max(`Description`) 
        FROM Orders ooooiiiii 
        WHERE ooooiiiii.ordernumber = Orders.ordernumber
          AND rownumber = 3) as `Row3`,
      ( SELECT max(`Description`) 
        FROM Orders ooooiiiii 
        WHERE ooooiiiii.ordernumber = Orders.ordernumber
          AND rownumber = 4) as `Row4`,
      ( SELECT max(`Description`) 
        FROM Orders ooooiiiii 
        WHERE ooooiiiii.ordernumber = Orders.ordernumber
          AND rownumber = 5) as `Row5`
    FROM Orders
    WHERE rownumber BETWEEN 0 and 5
    GROUP BY ordernumber;
    The reason for max() is that anything is greater than null iirc.
    Hey it's not pretty, but I've done it before no shame in my game.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles