有这样一组有规则的数据
ID Item Sortid
1 A 1
1 B 2
1 C 3
2 D 1
2 E 2
2 F 3
3 G 1
3 H 2
3 I 3
纵转横(将SortID 纵转横) ,结果为
ID 1 2 3
1 A B C
2 D E F
3 G H I SQL 应该怎么写? 高手们,秀秀技术吧
ID Item Sortid
1 A 1
1 B 2
1 C 3
2 D 1
2 E 2
2 F 3
3 G 1
3 H 2
3 I 3
纵转横(将SortID 纵转横) ,结果为
ID 1 2 3
1 A B C
2 D E F
3 G H I SQL 应该怎么写? 高手们,秀秀技术吧
max(case when Sortid=1 then item else '' end) as [1],
max(case when Sortid=2 then item else '' end) as [2],
max(case when Sortid=3 then item else '' end) as [3]
from tb
group by id
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
id int ,
Item varchar(10),
Sortid int
)
GO
INSERT TB
select '1','A','1' union all
select '1','B','2' union all
select '1','C','3' union all
select '2','D','1' union all
select '2','E','2' union all
select '2','F','3' union all
select '3','G','1' union all
select '3','H','2' union all
select '3','I','3'
--查询
select id,
max(case when Sortid=1 then item else '' end) as [1],
max(case when Sortid=2 then item else '' end) as [2],
max(case when Sortid=3 then item else '' end) as [3]
from tb
group by id
--结果
/*(9 行受影响)
id 1 2 3
----------- ---------- ---------- ----------
1 A B C
2 D E F
3 G H I(3 行受影响)
*/
go
create table [tb] (ID int,Item varchar(1),Sortid int)
insert into [tb]
select 1,'A',1 union all
select 1,'B',2 union all
select 1,'C',3 union all
select 2,'D',1 union all
select 2,'E',2 union all
select 2,'F',3 union all
select 3,'G',1 union all
select 3,'H',2 union all
select 3,'I',3
select ID,
max(case when Sortid=1 then item else '' end)[1],
max(case when Sortid=2 then item else '' end)[3],
max(case when Sortid=3 then item else '' end)[3]
from tb
group by id
/*
ID 1 3 3
----------- ---- ---- ----
1 A B C
2 D E F
3 G H I(3 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[Item] varchar(1),[Sortid] int)
insert [tb]
select 1,'A',1 union all
select 1,'B',2 union all
select 1,'C',3 union all
select 2,'D',1 union all
select 2,'E',2 union all
select 2,'F',3 union all
select 3,'G',1 union all
select 3,'H',2 union all
select 3,'I',3--------------------------------查询开始------------------------------select * from [tb] pivot (max([Item]) for [Sortid] in ([1],[2],[3])) b
/*
ID 1 2 3
----------- ---- ---- ----
1 A B C
2 D E F
3 G H I(3 行受影响)
*/
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (ID int,Item varchar(1),Sortid int)
insert into #A
select 1,'A',1 union all
select 1,'B',2 union all
select 1,'C',3 union all
select 2,'D',1 union all
select 2,'E',2 union all
select 2,'F',3 union all
select 3,'G',1 union all
select 3,'H',2 union all
select 3,'I',3select ID,
MAX(CASE WHEN SORTID=1 THEN ITEM ELSE '' END ) AS [1],
MAX(CASE WHEN SORTID=2 THEN ITEM ELSE '' END ) AS [2],
MAX(CASE WHEN SORTID=3 THEN ITEM ELSE '' END ) AS [3]
from #A
GROUP BY ID(所影响的行数为 9 行)ID 1 2 3
----------- ---- ---- ----
1 A B C
2 D E F
3 G H I(所影响的行数为 3 行)
go
create table [tb]([ID] int,[Item] varchar(1),[Sortid] int)
insert [tb]
select 1,'A',1 union all
select 1,'B',2 union all
select 1,'C',3 union all
select 2,'D',1 union all
select 2,'E',2 union all
select 2,'F',3 union all
select 3,'G',1 union all
select 3,'H',2 union all
select 3,'I',3declare @sql varchar(8000)select @sql = isnull(@sql+',','')+'max(case sortid when '+ltrim(sortid)+' then item else '''' end) as ['+ltrim(sortid)+']'
from (select distinct sortid from tb) tselect @sql = 'select id,'+@sql+' from tb group by id'exec(@sql)id 1 2 3
----------- ---- ---- ----
1 A B C
2 D E F
3 G H I
drop table tb
go
create table tb
(
ID int,
Item varchar(5),
Sortid int
)
go
insert into tb(ID,Item,Sortid)
values(1,'A',1)
insert into tb(ID,Item,Sortid)
values(1,'B',2)
insert into tb(ID,Item,Sortid)
values(1,'C',3)
insert into tb(ID,Item,Sortid)
values(2,'D',1)
insert into tb(ID,Item,Sortid)
values(2,'E',2)
insert into tb(ID,Item,Sortid)
values(2,'F',3)
insert into tb(ID,Item,Sortid)
values(3,'G',1)
insert into tb(ID,Item,Sortid)
values(3,'H',2)
insert into tb(ID,Item,Sortid)
values(3,'I',3)
go
select * from tb
go
select ID, max(case when Sortid=1 then item else '' end) '1',
max(case when Sortid=2 then item else '' end) '2',
max(case when Sortid=3 then item else '' end) '3' from tb group by ID
go
max(case when Sortid=1 then item else '' end)[1],
max(case when Sortid=2 then item else '' end)[2],
max(case when Sortid=3 then item else '' end)[3]
from tb
group by id