行转列(动态列)

create table #
(
    id int identity,
    pid varchar(100),
    comid varchar(100)
)
insert #
select '001', 'a' union all
select '001', 'b' union all
select '002', 'b' union all
select '003', 'c' union all
select '003', 'd' union all
select '003', 'd' union all
select '004', 'f'
--SQL
declare @sql varchar(max)
set @sql = ''
select @sql = @sql + ',' + QUOTENAME(comid) from (select distinct comid from #) a
print @sql
set @sql = '
select * from
(select pid, pidcopy = pid, comid from #) a
pivot
(count(pidcopy) for comid in('+ STUFF(@sql, 1, 1, '') +')) b
'
print @sql
exec(@sql)