CREATE TABLE tbc ( col1 VARCHAR(10), col2 INT )GOINSERT INTO tbc SELECT 'A', 1 UNION SELECT 'A', 2 UNION SELECT 'B', 3 UNION SELECT 'B', 4WITH table1 AS (SELECT col1,col2,num=ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) FROM tbc)SELECT DISTINCT col1, (SELECT col2 FROM table1 AS tbaa WHERE tbaa.col1 = tbbb.col1 AND tbaa.num = 1) AS col21, (SELECT col2 FROM table1 AS tbaa WHERE tbaa.col1 = tbbb.col1 AND tbaa.num = 2) AS col22 FROM table1 AS tbbbcol1 col21 col22 A 1 2 B 3 4
use Tempdb go --> -->
if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([col1] nvarchar(1),[col2] int) Insert #T select N'A',1 union all select N'A',2 union all select N'B',3 union all select N'B',4 Go DECLARE @i VARCHAR(4),@s NVARCHAR(1000)Select TOP 1 @s='',@i=COUNT(*) from #T GROUP BY [col1] ORDER BY COUNT(*) DESC WHILE @i>0 SELECT @s=',[Col'+@i+']=max(case when Row='+@i+' then [col2] end)'+@s,@i=@i-1 EXEC('SELECT [col1]'+@s+' FROM (SELECT *,Row=ROW_NUMBER()OVER(PARTITION BY [col1] ORDER BY [col1])FROM #T)t GROUP BY [col1]')/* A 1 2 B 3 4*/
if not object_id(N'tb') is null drop table tb Go CREATE TABLE tb ( col1 VARCHAR(10), col2 INT ) GOINSERT INTO tb SELECT 'A', 1 UNION all SELECT 'A', 2 UNION all SELECT 'B', 3 UNION all SELECT 'B', 4;-- 静态pivot方法 select col1, [1] as col21, [2] as col22 from ( select rn= row_number() over (partition by col1 order by col1) , * from tb ) t pivot ( max(col2) for rn in([1], [2]) ) pt
-- 动态pivot方法declare @sql varchar(8000) select @sql = isnull(@sql + '],[' , '') + cast(rn as varchar(10)) from ( select rn= row_number() over (partition by col1 order by col1) , * from tb ) t group by rn; set @sql = '[' + @sql + ']'; set @sql = ' select * from ( select rn= row_number() over (partition by col1 order by col1) , * from tb ) t pivot ( max(col2) for rn in (' + @sql + ') ) as pvt'; exec (@sql)
select col1,col21 = max(case when rownum = 1 then xjqid else '' end ), col22= max(case when rownum = 2 then xjqid else '' end ) from (select row_number() over (order by col2) as rownum,col1,col2 from table where 1=1 ) as b group by b.col1
CREATE TABLE tbc
(
col1 VARCHAR(10),
col2 INT
)GOINSERT INTO tbc
SELECT 'A', 1 UNION
SELECT 'A', 2 UNION
SELECT 'B', 3 UNION
SELECT 'B', 4WITH table1 AS
(SELECT col1,col2,num=ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2)
FROM tbc)SELECT DISTINCT col1,
(SELECT col2 FROM table1 AS tbaa WHERE tbaa.col1 = tbbb.col1 AND tbaa.num = 1) AS col21,
(SELECT col2 FROM table1 AS tbaa WHERE tbaa.col1 = tbbb.col1 AND tbaa.num = 2) AS col22
FROM table1 AS tbbbcol1 col21 col22
A 1 2
B 3 4
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([col1] nvarchar(1),[col2] int)
Insert #T
select N'A',1 union all
select N'A',2 union all
select N'B',3 union all
select N'B',4
Go
DECLARE @i VARCHAR(4),@s NVARCHAR(1000)Select TOP 1 @s='',@i=COUNT(*) from #T GROUP BY [col1] ORDER BY COUNT(*) DESC WHILE @i>0
SELECT @s=',[Col'+@i+']=max(case when Row='+@i+' then [col2] end)'+@s,@i=@i-1
EXEC('SELECT [col1]'+@s+'
FROM (SELECT *,Row=ROW_NUMBER()OVER(PARTITION BY [col1] ORDER BY [col1])FROM #T)t GROUP BY [col1]')/*
A 1 2
B 3 4*/
为什么呢?
是因为只有 SQL2005 及以后版本支持吗?还是因为个人习惯,或者效率的问题呢?pivot 也同时支持静态和动态的写法。
drop table tb
Go
CREATE TABLE tb
(
col1 VARCHAR(10),
col2 INT
)
GOINSERT INTO tb
SELECT 'A', 1 UNION all
SELECT 'A', 2 UNION all
SELECT 'B', 3 UNION all
SELECT 'B', 4;-- 静态pivot方法
select col1, [1] as col21, [2] as col22
from ( select rn= row_number() over (partition by col1 order by col1) , *
from tb
) t
pivot
(
max(col2) for rn
in([1], [2])
) pt
select @sql = isnull(@sql + '],[' , '') + cast(rn as varchar(10))
from ( select rn= row_number() over (partition by col1 order by col1) , *
from tb
) t
group by rn;
set @sql = '[' + @sql + ']';
set @sql =
' select *
from ( select rn= row_number() over (partition by col1 order by col1) , *
from tb
) t
pivot
(
max(col2) for rn
in (' + @sql + ')
) as pvt';
exec (@sql)
col22= max(case when rownum = 2 then xjqid else '' end )
from (select row_number() over (order by col2) as rownum,col1,col2 from table
where 1=1 ) as b
group by b.col1