SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Rows to columns

  1. #1
    SitePoint Addict
    Join Date
    Jun 2006
    Location
    Durban, South Africa
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rows to columns

    Hi all

    I have a bit of a strange query I need to do and need some help.

    Lets say I have a table that looks like

    tab1
    ipkTab1
    ifkCompany
    sCode
    sNum

    Lets says this table is populated like so

    ipkTab1 , ifkCompany , sCode , sNum
    1 , 1 , NT , 01
    2 , 1 , OT , 02
    3 , 1 , PUB , 03
    4 , 1 , SUN , 04
    5 2 NT 01
    6 2 TA 02
    7 2 SA 04

    Now lets say I have another table, like so

    tab2
    ifkCompany
    N01
    N02
    N03
    N04

    I need to get all of the code values, from the first table, into the second table, where all code values for one company must be in one row, like so

    ifkCompany , N01 , N02 , N03 , N04
    1 , NT , OT , PUB , SUN
    2 , NT , TA , , SA

    I created some ridiculous way of doing this, but its not very reliable. The only other way I can think of doing it, is manually... In other words, creating a query to get the code values for a company where sNum = 01, then another query where sNum = 02 etc...

    Is there a better way to do this?

    Thanks in advance

  2. #2
    SitePoint Addict
    Join Date
    Jun 2006
    Location
    Durban, South Africa
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Found a solution.
    Don't know if there is a better way, but here goes

    Code:
    INSERT INTO tab2 (ifkCompany , N01 , N02 , N03 , N04)
    SELECT ifkCompany
         , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '01' THEN sCode ELSE '' END)) AS N01
         , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '02' THEN sCode ELSE '' END)) AS N02
         , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '03' THEN sCode ELSE '' END)) AS N02
         , TRIM(BOTH ',' FROM GROUP_CONCAT(CASE sNum WHEN '04' THEN sCode ELSE '' END)) AS N02
    FROM tab1
    WHERE ifkCompany = X
    GROUP BY ifkCompany;
    Anyone got anything better?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT ifkCompany
         , MAX(CASE WHEN sNum = '01' THEN sCode END) AS N01
         , MAX(CASE WHEN sNum = '02' THEN sCode END) AS N02
         , MAX(CASE WHEN sNum = '03' THEN sCode END) AS N03
         , MAX(CASE WHEN sNum = '04' THEN sCode END) AS N04
    FROM tab1
    GROUP BY ifkCompany;
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Jun 2006
    Location
    Durban, South Africa
    Posts
    287
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, I don't know why that works, but its does

    Thanks


Bookmarks

Posting Permissions

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