Dynamic columns using pivot

I have a table that looks like this:

Main table

id	bomname	styleid
1	bom1	101
2	bom2	102
3	bom3	103

this is detail table

Id	bomId	bomKey	bomValue
1	1	part	cllr
2	1	unit	kg
3	1	qty	123
4	1	part	body
5	1	unit	kg
6	1	qty	456
7	2	part	slm
8	2	unit	kg
9	2	qty	789
10	3	part	abc
11	3	unit	kg
12	3	qty	789
13	3	color	red

I hope query styleid=101 show this data list using PIVOT:

part	unit	qty	ids
cllr	kg	123	[1,2,3]
body	kg	456	[4,5,6]

I hope query styleid=103 show this data list using PIVOT:

part	unit	qty	color	ids
abc	kg	789	red	[10,11,12,13]

This is my SQL code:

DECLARE @sql nvarchar(max) = '', @col_list nvarchar(max) = ''

SET @col_list = (SELECT DISTINCT QUOTENAME(bomKey) + ','  
                 FROM BomDetail 
                 FOR XML PATH(''))

SET @col_list = LEFT(@col_list, LEN(@col_list) - 1)

SET @sql = 'SELECT ' + @col_list + 
           ' FROM
                 (SELECT bomKey, bomValue FROM BomDetail) x
             PIVOT
                 (MAX([bomValue]) FOR [bomKey] IN (' + @col_list + ')
            ) AS p'

EXEC(@sql)

but now query returns only one row of data, how to modify my SQL code?

part	unit	qty
body	kg	456

Answer

Please try with this!

DECLARE @sql nvarchar(max) = '', @col_list nvarchar(max) = ''

SET @col_list = (SELECT DISTINCT QUOTENAME(a.bomKey) + ','  
                 FROM DetailTable a join MainTable b on (a.bomId = b.id)
                 where b.styleid=101 --> CHANGE IT
                 FOR XML PATH(''))

SET @col_list = LEFT(@col_list, LEN(@col_list) - 1)

If Object_id('Tempdb..#Details') is not null
    Drop Table #Details

Create Table #Details (Seq Int, bomId Int,bomkey varchar(20), bomvalue varchar(20),id Int)

Insert #Details
select Row_Number() Over(Partition by d.bomkey Order by d.bomkey) Seq, d.bomId,d.bomkey, d.bomvalue,d.id 
from DetailTable d join MainTable m
on (d.bomId = m.id)
where styleid=101   --> CHANGE IT
Order by 1


SET @sql = 'SELECT ' + @col_list + 
           ',Ids FROM
                 (select bomkey, bomvalue, ''['' + stuff((select '','' + cast(id as varchar(100))  from #Details b where b.seq = a.seq for xml path('''')),1,1,'''') + '']'' Ids from #Details a ) x
             PIVOT
                 (MAX([bomvalue]) FOR [bomKey] IN ('+@col_list+')
            ) AS p'

EXEC(@sql)

Note:

1. Replace the StyleId in TWO Places in this code
2. Replace with MainTable and DetailTable accordingly