Karthik Nishanth Karthik Nishanth - 2 months ago 6
SQL Question

SQL Column title from another table

I have two tables

Index | Name | GroupIndex \\table1
1 | A | 1
2 | A | 1
3 | A | 2

Index | GroupName \\table2
1 | G1
2 | G2


I would like my result to be

Index | Name | G1 | G2
1 | A | 2 | 1


where I need to display the number of entries of
Name
grouped by
GroupIndex
, but in a column format.

I couldn't find a way to add columns dynamically, from another table. Can you help me out?

Answer
create table #table1 ([Index] int,Name char(1), GroupIndex int)
create table #table2 ([Index] int,GroupName char(2))

insert into #table1 values
(1,'A',1),
(2,'A',1),
(3,'A',2)

insert into #table2 values
(1,'G1'),
(2,'G2')

--select * from #table1
--select * from #table2

select * from
    (select t1.[GroupIndex] as [Index], t1.Name, t2.[Index] as gi, t2.GroupName 
    from #table1 t1
    inner join #table2 t2 on t2.[Index] = t1.[Index]) as s
PIVOT
    (max(gi) FOR GroupName in (G1,G2)) as pvt

--RESULTS--

Index   Name    G1  G2
1       A       1   2