none
Dynamic columns

    Question

  • Table A

    ID          Name

    1            abc

    2            def

    3            ghi

    Table B

    ID         SubID        SubName

    1           1               xxx

    2           1               yyy

    2           2               zzz

    2           3               aaa

    I require the information in the following format -

    ID        SubID1        Subname1          SubID2           SubName2          SubID3          SubName3

    1          1                 xxx

    1          1                 yyy                    2                    zzz                      3                  aaa

    Thanks in advance!


    -Saravanan

    Thursday, November 14, 2013 3:30 PM

Answers

  • Solution 1:

    declare @tA table (ID int, [Name] varchar(10));
    insert into @tA 
    values 
    (1,            'abc'),
    (2            , 'def'),
    (3            , 'ghi');
    
    declare @tB table (ID int,   SubID int,  [SubName] varchar(10));
    insert @tb values
    (1 ,          1               , 'xxx'),
    (2  ,         1               , 'yyy'),
    (2   ,        2               , 'zzz'),
    (2    ,       3               , 'aaa');
    
    
    select a.ID 
    , b1.SubID as SubID1, b1.SubName as SubName1
    , b2.SubID as SubID2, b2.SubName as SubName2
    , b3.SubID as SubID3, b3.SubName as SubName3
    , b4.SubID as SubID4, b4.SubName as SubName4
    from @tA a
    left join @tB b1
    on b1.id = a.id 
      and b1.SubID = 1
    left join @tB b2
    on b2.id = a.id 
      and b2.SubID = 2
    left join @tB b3
    on b3.id = a.id 
      and b3.SubID = 3
    left join @tB b4
    on b4.id = a.id 
      and b4.SubID = 4
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, November 14, 2013 4:11 PM
  • Solution 2

    declare @tA table (ID int, [Name] varchar(10));
    insert into @tA 
    values 
    (1,            'abc'),
    (2            , 'def'),
    (3            , 'ghi');
    
    
    declare @tB table (ID int,   SubID int,  [SubName] varchar(10));
    insert @tb values
    (1 ,          1               , 'xxx'),
    (2  ,         1               , 'yyy'),
    (2   ,        2               , 'zzz'),
    (2    ,       3               , 'aaa');
    
    
    with cteSubID as (
    select ID , [1] as SubID1, [2] as SubID2, [3] as SubID3
    from (Select ID, SubID from @tb) as  b
    pivot (max(SubID) for SubID in ([1], [2], [3]) ) pvtSubID
    
    ), cteSubName as (
    select ID , [1] as SubName1, [2] as SubName2, [3] as SubName3
    from  @tb as  b
    pivot (max(SubName) for SubID in ([1], [2], [3]) ) pvtSubID
    
    )
    select * from @ta a 
    left outer join cteSubName sn
    on a.ID = sn.id 
    left outer join cteSubID  si
    on a.ID = si.id 
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, November 14, 2013 4:18 PM
  • Try this,

    CREATE TABLE #TableA  (ID INT, Name VARCHAR(10))
    INSERT INTO #TableA VALUES(1,'abc')
    INSERT INTO #TableA VALUES(2,'def')
    INSERT INTO #TableA VALUES(3,'ghi')

    CREATE TABLE #TableB (ID INT, SubID int, SubName VARCHAR(10))
    INSERT INTO #TableB VALUES(1,1,'xxx')
    INSERT INTO #TableB VALUES(2,1,'yyy')
    INSERT INTO #TableB VALUES(2,2,'zzz')
    INSERT INTO #TableB VALUES(2,3,'aaa')

    ;WITH CTE_M AS (
     SELECT ID,SubID, SubName,'SubID' + CAST(SubID AS VARCHAR(4)) SubIDCol, 'SubName' + CAST(SubID AS VARCHAR(4)) SubNameCol 
     FROM #TableB
    ),CTE_S1 AS (
     SELECT * FROM (SELECT ID,SubID,SubIDCol FROM CTE_M) X
     PIVOT
     (
      MIN(SubID) FOR SubIDCol IN (SubID1,SubID2,SubID3)
     ) PVT
    ),CTE_S2 AS (
     SELECT * FROM (SELECT ID,SubName,SubNameCol FROM CTE_M) X
     PIVOT
     (
      MIN(SubName) FOR SubNameCol IN (SubName1,SubName2,SubName3)
     ) PVT
    )
    SELECT * FROM CTE_S1 A INNER JOIN CTE_S2 B ON A.ID = B.ID

     

    Regards, RSingh


    Thursday, November 14, 2013 4:25 PM

All replies

  • Solution 1:

    declare @tA table (ID int, [Name] varchar(10));
    insert into @tA 
    values 
    (1,            'abc'),
    (2            , 'def'),
    (3            , 'ghi');
    
    declare @tB table (ID int,   SubID int,  [SubName] varchar(10));
    insert @tb values
    (1 ,          1               , 'xxx'),
    (2  ,         1               , 'yyy'),
    (2   ,        2               , 'zzz'),
    (2    ,       3               , 'aaa');
    
    
    select a.ID 
    , b1.SubID as SubID1, b1.SubName as SubName1
    , b2.SubID as SubID2, b2.SubName as SubName2
    , b3.SubID as SubID3, b3.SubName as SubName3
    , b4.SubID as SubID4, b4.SubName as SubName4
    from @tA a
    left join @tB b1
    on b1.id = a.id 
      and b1.SubID = 1
    left join @tB b2
    on b2.id = a.id 
      and b2.SubID = 2
    left join @tB b3
    on b3.id = a.id 
      and b3.SubID = 3
    left join @tB b4
    on b4.id = a.id 
      and b4.SubID = 4
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, November 14, 2013 4:11 PM
  • Solution 2

    declare @tA table (ID int, [Name] varchar(10));
    insert into @tA 
    values 
    (1,            'abc'),
    (2            , 'def'),
    (3            , 'ghi');
    
    
    declare @tB table (ID int,   SubID int,  [SubName] varchar(10));
    insert @tb values
    (1 ,          1               , 'xxx'),
    (2  ,         1               , 'yyy'),
    (2   ,        2               , 'zzz'),
    (2    ,       3               , 'aaa');
    
    
    with cteSubID as (
    select ID , [1] as SubID1, [2] as SubID2, [3] as SubID3
    from (Select ID, SubID from @tb) as  b
    pivot (max(SubID) for SubID in ([1], [2], [3]) ) pvtSubID
    
    ), cteSubName as (
    select ID , [1] as SubName1, [2] as SubName2, [3] as SubName3
    from  @tb as  b
    pivot (max(SubName) for SubID in ([1], [2], [3]) ) pvtSubID
    
    )
    select * from @ta a 
    left outer join cteSubName sn
    on a.ID = sn.id 
    left outer join cteSubID  si
    on a.ID = si.id 
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, November 14, 2013 4:18 PM
  • Try this,

    CREATE TABLE #TableA  (ID INT, Name VARCHAR(10))
    INSERT INTO #TableA VALUES(1,'abc')
    INSERT INTO #TableA VALUES(2,'def')
    INSERT INTO #TableA VALUES(3,'ghi')

    CREATE TABLE #TableB (ID INT, SubID int, SubName VARCHAR(10))
    INSERT INTO #TableB VALUES(1,1,'xxx')
    INSERT INTO #TableB VALUES(2,1,'yyy')
    INSERT INTO #TableB VALUES(2,2,'zzz')
    INSERT INTO #TableB VALUES(2,3,'aaa')

    ;WITH CTE_M AS (
     SELECT ID,SubID, SubName,'SubID' + CAST(SubID AS VARCHAR(4)) SubIDCol, 'SubName' + CAST(SubID AS VARCHAR(4)) SubNameCol 
     FROM #TableB
    ),CTE_S1 AS (
     SELECT * FROM (SELECT ID,SubID,SubIDCol FROM CTE_M) X
     PIVOT
     (
      MIN(SubID) FOR SubIDCol IN (SubID1,SubID2,SubID3)
     ) PVT
    ),CTE_S2 AS (
     SELECT * FROM (SELECT ID,SubName,SubNameCol FROM CTE_M) X
     PIVOT
     (
      MIN(SubName) FOR SubNameCol IN (SubName1,SubName2,SubName3)
     ) PVT
    )
    SELECT * FROM CTE_S1 A INNER JOIN CTE_S2 B ON A.ID = B.ID

     

    Regards, RSingh


    Thursday, November 14, 2013 4:25 PM