# 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

-Saravanan

Thursday, November 14, 2013 3:30 PM

• 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
```

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
```

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
```

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
```

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