if object_id('tb') is not null
drop table tb
go
create table tb([颜色] nvarchar(10),[大小] varchar(10), [大小ID] int, [数量] int)
insert tb select N'红色','X', 1,10
insert tb select N'红色','S', 2,20
insert tb select N'红色','M', 3,30
insert tb select N'白色','S', 2,40
insert tb select N'白色','M', 3, 50
godeclare @s nvarchar(4000)
select @s=N'select 颜色 '
select @s=@s+N',max(case 大小 when N'''+大小+N''' then 数量 else 0 end) ['+大小+']'
from (select distinct 大小,case 大小 WHEN 'X' THEN 0 WHEN 'S' THEN 1 WHEN 'M' THEN 2 END AS ORD from tb) as a
ORDER BY ORD
select @s=@s+N' from tb group by 颜色'
exec(@s)
/*
颜色 X S M
---------- ----------- ----------- -----------
白色 0 40 50
红色 10 20 30
*/
drop table tb
go
create table tb([颜色] nvarchar(10),[大小] varchar(10), [大小ID] int, [数量] int)
insert tb select N'红色','X', 1,10
insert tb select N'红色','S', 2,20
insert tb select N'红色','M', 3,30
insert tb select N'白色','S', 2,40
insert tb select N'白色','M', 3, 50
godeclare @s nvarchar(4000)
select @s=N'select 颜色 '
select @s=@s+N',max(case 大小 when N'''+大小+N''' then 数量 else 0 end) ['+大小+']'
from (select distinct 大小,case 大小 WHEN 'X' THEN 0 WHEN 'S' THEN 1 WHEN 'M' THEN 2 END AS ORD from tb) as a
ORDER BY ORD
select @s=@s+N' from tb group by 颜色'
exec(@s)
/*
颜色 X S M
---------- ----------- ----------- -----------
白色 0 40 50
红色 10 20 30
*/
drop table tb
go
create table tb([颜色] varchar(10),[大小] varchar(10), [大小ID] int, [数量] int)
insert tb select '红色','X', 1,10
insert tb select '红色','S', 2,20
insert tb select '红色','M', 3,30
insert tb select '白色','S', 2,40
insert tb select '白色','M', 3, 50
godeclare @s varchar(8000)DECLARE @t TABLE([大小] varchar(10),[大小ID] int)
INSERT @t
SELECT DISTINCT [大小],[大小ID]
FROM tb
ORDER BY [大小ID];select @s='select 颜色 '
select @s=@s+',max(case 大小 when '''+大小+''' then 数量 else 0 end) ['+大小+']'
from (select 大小 from @t) as a
select @s=@s+' from tb group by 颜色'
exec(@s)
drop table tb
go
create table tb([颜色] varchar(10),[大小] varchar(10), [大小ID] int, [数量] int)
insert tb select '红色','X', 1,10
insert tb select '红色','S', 2,20
insert tb select '红色','M', 3,30
insert tb select '白色','S', 2,40
insert tb select '白色','M', 3, 50
go
-->开始查询
declare @s varchar(8000)
select @s='select 颜色 '
select @s=@s+',max(case 大小 when '''+大小+''' then 数量 else 0 end) ['+大小+']'
from (select 大小 from (select top 100 percent 大小,大小ID from tb group by 大小,大小ID order by 大小ID) t) as a
select @s=@s+' from tb group by 颜色'
exec(@s)
/*
颜色 M S X
---------- ----------- ----------- -----------
白色 50 40 0
红色 30 20 10(2 行受影响)*/
select @s='select 颜色 '
select @s=@s+',max(case 大小 when '''+大小+''' then 数量 else 0 end) ['+大小+']'
from (select distinct 大小,[大小ID] from tb) as a order by [大小ID]
select @s=@s+' from tb group by 颜色' exec(@s) /**
颜色 X S M
---------- ----------- ----------- -----------
白色 0 40 50
红色 10 20 30
**/