现有表结构/*
表结构为:
a
----
A
B
C
D
E
F
G
需要的结果是:
a b c d e f g
---- ---- ---- ---- ---- ---- ----
A B C D E F G
*/
表结构为:
a
----
A
B
C
D
E
F
G
需要的结果是:
a b c d e f g
---- ---- ---- ---- ---- ---- ----
A B C D E F G
*/
max(case a when a then a else '' end) as a,
max(case b when a then a else '' end) as b,
max(case c when a then a else '' end) as c,
max(case d when a then a else '' end) as d,
max(case e when a then a else '' end) as e,
max(case f when a then a else '' end) as f,
max(case g when a then a else '' end) as g
from
tb
max(case a when 'a' then a else '' end) as a,
max(case a when 'b' then a else '' end) as b,
max(case a when 'c' then a else '' end) as c,
max(case a when 'd' then a else '' end) as d,
max(case a when 'e' then a else '' end) as e,
max(case a when 'f' then a else '' end) as f,
max(case a when 'g' then a else '' end) as g
from
tb
http://www.cnblogs.com/insus/articles/1969896.html
http://www.cnblogs.com/insus/articles/1970577.html
http://www.cnblogs.com/insus/articles/1970707.html
http://www.cnblogs.com/insus/articles/1971446.html
表结构为:
ID a
----------- ----
1 A
2 B
3 C
4 D
5 E
6 F
7 G需要的结果是:
1 2 3 4 5 6 7
---- ---- ---- ---- ---- ---- ----
A B C D E F G
*/
use testif object_id('tb') is not null drop table tb
GO
create table tb(ID int identity(1,1),aa varchar(1))
insert into tb
select 'A' union all
select 'B' union all
select 'C' union all
select 'D' union all
select 'E' union all
select 'F' union all
select 'G'
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + aa from tb group by aa
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) t pivot (max(ID) for aa in (' + @sql + ')) b')
use tempdb;
/*
create table t1
(
ID int not null,
a nvarchar(10) not null
);
insert into t1(ID,a)
values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'),
(6,'F'),
(7,'G');
*/
select
MAX(case when ID = 1 then 'A' end) as [1],
MAX(case when ID = 2 then 'B' end) as [2],
MAX(case when ID = 3 then 'C' end) as [3],
MAX(case when ID = 4 then 'D' end) as [4],
MAX(case when ID = 5 then 'E' end) as [5],
MAX(case when ID = 6 then 'F' end) as [6],
MAX(case when ID = 7 then 'G' end) as [7]
from t1;
表结构为:
ID a
----------- ----
5 E
6 F
3 C
4 D
1 A
2 B
7 G需要的结果是:
1 2 3 4 5 6 7
---- ---- ---- ---- ---- ---- ----
A B C D E F G
表结构为:
ID a
----------- ----
1 E
2 F
3 C
4 D
5 A
6 B
7 G需要的结果是:
1 2 3 4 5 6 7
---- ---- ---- ---- ---- ---- ----
E F C D A B G
GO
create table tb(ID int ,aa varchar(1))
insert into tb
select 5,'E' union all
select 6,'F' union all
select 3,'C' union all
select 4,'D' union all
select 1,'A' union all
select 2,'B' union all
select 7,'G'会自动排序的,你也可以加个order by ID指定排序
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + cast (id as varchar) from tb group by id order by ID set @sql = '[' + @sql + ']'exec ('select * from tb pivot (max(aa) for id in (' + @sql + ')) b')
use testif object_id('tb') is not null drop table tb
GO
create table tb(ID int,aa varchar(10))
insert into tb
select 5,'E' union all
select 6,'F' union all
select 3,'C' union all
select 4,'D' union all
select 1,'A' union all
select 2,'B' union all
select 7,'G'declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + convert(varchar(10),ID) from tb group by ID
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) t pivot (max(aa) for ID in (' + @sql + ')) b')--查询结果
--1 2 3 4 5 6 7
------------ ---------- ---------- ---------- ---------- ---------- ----------
--A B C D E F G
--
--(1 行受影响)