Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Question Unanswered: Advanced SQL HELP: transform rows to columns

    I have a table of 1000+ rows with 100+ different items with each Id having max 10 items

    Id item
    1 a
    1 b
    2 c
    2 a
    2 d
    3 b
    3 z

    each item has an order in a different lookup table
    item item_seq
    a 1
    b 2
    c 3
    d 4


    The item columns (below) need to use the item order from lookup table, so the output table is as follows

    Id item item2 item3
    1 a b
    2 a c d
    3 b z

    PLEASE HELP !!!

  2. #2
    Join Date
    Feb 2004
    Location
    Egypt
    Posts
    12

    Re: Advanced SQL HELP: transform rows to columns

    Hello,
    I am a little bit confused about your target but what I can catch is that you are asking how to make an output table like the one you demonstrated at the end. If it is so, I think that the table design itself is not correct and what you need is to make a one to many relation ship table composed of 2 fields that looks like the following

    Id Item
    1 a
    1 b
    2 a
    2 c
    2 d
    3 b
    3 Z
    and so on

    and by this way you can insert as many as items as you like with each Id

    Thanks

  3. #3
    Join Date
    Mar 2004
    Posts
    6

    Unhappy Re: Advanced SQL HELP: transform rows to columns

    Thanks vm for replying Rasha.

    To make matters simpler, I exclude the item order table since I found a way of doing that.
    So basically the input table is
    Id Item
    1 a
    1 b
    2 a
    2 c
    2 d
    3 b
    3 Z



    and the output table I need to come up with should be like

    Id Item1 Item2 Item3
    1 a b
    2 a c d
    3 b z

    The CASE statement is diffucilt to use since there are 100+ different items (and new ones are added all the time) and each id can have 1 to 9 items, so columns Item1 to Item9 per id are needed.

    Any ideas ?


    Originally posted by rasha
    Hello,
    I am a little bit confused about your target but what I can catch is that you are asking how to make an output table like the one you demonstrated at the end. If it is so, I think that the table design itself is not correct and what you need is to make a one to many relation ship table composed of 2 fields that looks like the following

    Id Item
    1 a
    1 b
    2 a
    2 c
    2 d
    3 b
    3 Z
    and so on

    and by this way you can insert as many as items as you like with each Id

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What you are asking for is called a cross tab query. The pivot is best handled on the client if at all possible, but it can also be done in SQL when you can't do it on the client.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    Egypt
    Posts
    12
    Hi, I do not know much about Cross tab query but I think the following dissccusion may help you:
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19008

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that your item values are sortable for each id (that there aren't any NULL values and no duplicates), you can use:
    PHP Code:
    CREATE TABLE dbo.table00 (
       
    id        INT        NOT NULL
    ,  item        CHAR(1)        NOT NULL
       
    )

    INSERT INTO dbo.table00 (iditem)
       
    SELECT           1'a'
       
    UNION ALL SELECT 1'b'
       
    UNION ALL SELECT 2'a'
       
    UNION ALL SELECT 2'c'
       
    UNION ALL SELECT 2'd'
       
    UNION ALL SELECT 3'b'
       
    UNION ALL SELECT 3'z'

    SELECT a.idMin(a.item
    +  
    Coalesce(', ' Min(b.item), ''
    +  
    Coalesce(', ' Min(c.item), '')
       
    FROM dbo.table00 AS a
       LEFT JOIN dbo
    .table00 AS b
          ON 
    (b.id a.id
          
    AND a.item b.item)
       
    LEFT JOIN dbo.table00 AS c
          ON 
    (c.id a.id
          
    AND b.item c.item)
       
    GROUP BY a.id
       ORDER BY a
    .id

    DROP TABLE dbo
    .table00 
    -PatP

  7. #7
    Join Date
    Mar 2004
    Posts
    6

    Wink

    Thanks very much for your reply PAT.

    I have to say your approach with the LEFT join and item> comparisons
    is simple and clever.
    However bearing in mind that there are 100+ different items, doing 100+ left joins...I don't how efficient will that be...
    Thanks very much anyway.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by dim2000
    However bearing in mind that there are 100+ different items, doing 100+ left joins...I don't how efficient will that be...
    Try it. You might be shocked (I know that I was).

    A cross tab is still better handled on the client when that is possible, but the server does a better job than I thought that it would.

    -PatP

  9. #9
    Join Date
    Mar 2004
    Posts
    6
    Thanks for your reply Pat.

    The left joins worked quite fast actually, on the server that is not the client.

    Do you know if I could use the Case or ISNULL statements for this.

    e.g. CASE when item='a' THEN item1 ELSE 'None' END AS 'MCQ Raw',

    I used the case statement before but only for amounts that needed to be summed up, when I tried to use it with text fields I could not get it to work,
    Any ideas?

Posting Permissions

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