locked
Multiple columns referencing another tables data RRS feed

  • Question

  • Hi,

    I have 2 tables as detailed below:

    ID     Description
    1       Description 1
    2       Description 2
    3       Description 3
    etc..... ID's are all unique

    and another table:
    EntryID       ID1   ID2   ID3    ID4   ID5
    10029         2       4       7       9       15

    Table 1 holds the descriptions and ID's and the second table holds a unique ID and the other columns hold an ID which are contained in the first table.

    What I want to do is get the ID and Descriptions returned..

    So the result would look like:

    100029   2    Description2
    100029   4    Description4
    100029   7    Description7
    100029   9    Description9
    100029   15  Description15

    Ive tried to explain this as lamely as possible I've tried joins but not quite got it.

    Thanks

    Saturday, October 17, 2009 8:48 PM

Answers

  • Try using the unpivot operator.

    ;with r_set as (
    select
        EntryID,
        ID
    from
        T1
        unpivot
        (
        ID
        for cn in ([ID1], [ID2], [ID3], [ID4], [ID5])
        ) as unpvt
    )
    select
        A.EntryID,
        A.ID,
        B.Description
    from
        r_set as A
        inner join
        T2 as B
        on A.ID = B.ID;
    GO


    If you are using a version earlier than 2005, then try doing the unpivoting using a cross join to a table with five rows.

    select
        A.EntryID,
        case B.c1
        when 1 then ID1
        when 2 then ID2
        when 3 then ID3
        when 4 then ID4
        when 5 then ID5
        end as ID
    from
       T1 as A
       cross join
       (select 1 as c1 union all select 2 union all select 3 union all select 4 union all select 5) as B;


    AMB
    • Marked as answer by Kalman Toth Saturday, October 17, 2009 9:17 PM
    Saturday, October 17, 2009 9:02 PM

All replies

  • Try using the unpivot operator.

    ;with r_set as (
    select
        EntryID,
        ID
    from
        T1
        unpivot
        (
        ID
        for cn in ([ID1], [ID2], [ID3], [ID4], [ID5])
        ) as unpvt
    )
    select
        A.EntryID,
        A.ID,
        B.Description
    from
        r_set as A
        inner join
        T2 as B
        on A.ID = B.ID;
    GO


    If you are using a version earlier than 2005, then try doing the unpivoting using a cross join to a table with five rows.

    select
        A.EntryID,
        case B.c1
        when 1 then ID1
        when 2 then ID2
        when 3 then ID3
        when 4 then ID4
        when 5 then ID5
        end as ID
    from
       T1 as A
       cross join
       (select 1 as c1 union all select 2 union all select 3 union all select 4 union all select 5) as B;


    AMB
    • Marked as answer by Kalman Toth Saturday, October 17, 2009 9:17 PM
    Saturday, October 17, 2009 9:02 PM
  • Thanks for the response,

    However, I dont actually understand how to relate this to my database structure. I have tried to map this to the tables however, I dont seem to be doing something right. (Im using 2005 and never used pivot/unpivot)

    I'm wondering if it makes more sense to detail the tables...

    Table 1: Category
    Has columns
    CategoryId and Description


    Table 2: CompanyCategory
    Has columns:
    EntryID
    SupplierID
    Category1
    Category2
    Category3
    Category4
    Category5
    Category6
    Category7
    Category8
    Category9
    Category10

    the category columns on table 2 have a value in each which are detailed in the Category table




    Saturday, October 17, 2009 10:05 PM
  • Try:

    ;with r_set as (
    select
        EntryID,
        CategoryId
    from
        CompanyCategory
        unpivot
        (
        CategoryId
        for cn in ([Category1], [Category2], [Category3], [Category4], [Category5], [Category6], [Category7], [Category8], [Category9], [Category10])
        ) as unpvt
    )
    select
        A.EntryID,
        A.CategoryId,
        B.Description
    from
        r_set as A
        inner join
        Category as B
        on A.CategoryId = B.CategoryId;
    GO
    Saturday, October 17, 2009 10:10 PM
  • I get the following error:
    The column name "Category1" specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.
    Saturday, October 17, 2009 10:37 PM
  • Can you copy and paste the code that is causing the error?


    Abdallah, PMP, MCTS
    Saturday, October 17, 2009 10:43 PM