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.
    I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;

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