if not object_id(N'Tempdb..#') is null drop table # Go Create table #([col1] int,[col2] nvarchar(1)) Insert # select 1,N'a' union all select 1,N'b' union all select 1,N'c' union all select 1,N'd' union all select 1,N'e' union all select 1,N'f' union all select 1,N'g' union all select 1,N'h' union ALL select 2,N'q' UNION ALL select 2,N'j' Go DECLARE @Row NVARCHAR(1000),@SQL NVARCHAR(4000) if not object_id(N'Tempdb..#2') is null drop table #2 SELECT ID=IDENTITY(INT,1,1), * INTO #2 from #SELECT TOP 1 @Row=COUNT(*) FROM #2 GROUP BY [col1] ORDER BY COUNT(*) descSET @SQL=''WHILE @Row>0 SELECT @SQL=',[Col'+@Row+']=max(case when Row='+@Row+' THEN Col2 else '''' end)'+@SQL,@Row=@Row-1 EXEC('select Col1'+@SQL+' from (select Row=(select count(*) from #2 where Col1=a.Col1 and ID<=a.ID),* from #2 as a) ta group by Col1')/* Col1 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 1 a b c d e f g h 2 q j */
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([col1] int,[col2] nvarchar(1))
Insert #
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 1,N'd' union all
select 1,N'e' union all
select 1,N'f' union all
select 1,N'g' union all
select 1,N'h' union ALL
select 2,N'q' UNION ALL
select 2,N'j'
Go
DECLARE @Row NVARCHAR(1000),@SQL NVARCHAR(4000)
if not object_id(N'Tempdb..#2') is null
drop table #2
SELECT ID=IDENTITY(INT,1,1), * INTO #2 from #SELECT TOP 1 @Row=COUNT(*) FROM #2 GROUP BY [col1] ORDER BY COUNT(*) descSET @SQL=''WHILE @Row>0
SELECT @SQL=',[Col'+@Row+']=max(case when Row='+@Row+' THEN Col2 else '''' end)'+@SQL,@Row=@Row-1
EXEC('select Col1'+@SQL+' from (select Row=(select count(*) from #2 where Col1=a.Col1 and ID<=a.ID),* from #2 as a) ta group by Col1')/*
Col1 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
1 a b c d e f g h
2 q j
*/