2个表 行转列 交叉表 Sqlserver 100分

[Dream_TT]

求大神指点

数据
表1：颜色表
Id 颜色 pid(是表2中pid的父ID)
1288322773 蓝色 0
1288322773 黑色 1
1288322773 玫红 2

表2：尺寸表
Id 数量 尺码 pid
1288322773 24 L 0
1288322773 18 XL 0
1288322773 21 XXL 0
1288322773 15 L 1
1288322773 18 XL 1
1288322773 19 XXL 1
1288322773 23 L 2
1288322773 20 XL 2
1288322773 27 XXL 2

[Dream_TT]

尺码和颜色都是不确定的 可能有10个颜色 也可能1个 尺码也是一样

[LongRui888]

引用 1 楼 Dream_TT 的回复:

尺码和颜色都是不确定的 可能有10个颜色 也可能1个 尺码也是一样

这个用动态语句来实现：

create table 颜色表(Id int,颜色 varchar(10), pid int)

insert into  颜色表
select 1288322773	,'蓝色'	,0  union all
select 1288322773	,'黑色'	,1 union all
select 1288322773	,'玫红'	,2

create table 尺寸表(Id int,数量 int, 尺码 varchar(10),  pid int)

insert into 尺寸表
select 1288322773	,24	,'L',	0 union all
select 1288322773	,18	,'XL',	0 union all
select 1288322773	,21	,'XXL',	0 union all
select 1288322773	,15	,'L',	1 union all
select 1288322773	,18	,'XL',	1 union all
select 1288322773	,19	,'XXL',	1 union all
select 1288322773	,23	,'L',	2 union all
select 1288322773	,20	,'XL',	2 union all
select 1288322773	,27	,'XXL',	2
go



declare @sql nvarchar(4000)

set @sql = ''

select @sql = @sql + ',MAX(case when 尺码='''+b.尺码+''' then 数量 else null end) ['+b.尺码+']'  
from 颜色表 a
left join 尺寸表 b
       on a.pid = b.pid and a.Id = b.Id
group by b.尺码


set @sql = 'select a.颜色'+ @sql +
           ' from 颜色表 a
			left join 尺寸表 b
				   on a.pid = b.pid and a.Id = b.Id
			group by a.颜色,b.pid
			order by b.pid'


exec(@sql)

/*
颜色	L	XL	XXL
蓝色	24	18	21
黑色	15	18	19
玫红	23	20	27
*/

[LongRui888]

上面动态语句，生成了类似这样的语句：

select  a.颜色,
		MAX(case when 尺码='L' then 数量 else null end) [L],
		MAX(case when 尺码='XL' then 数量 else null end) [XL],
		MAX(case when 尺码='XXL' then 数量 else null end) [XXL]
 
from 颜色表 a    
left join 尺寸表 b        
       on a.pid = b.pid and a.Id = b.Id    
group by a.颜色,b.pid    
order by b.pid

[xxfvba]

declare @s varchar(max) select @s=isnull(@s+',','')+'['+尺码+']' from 尺寸表 group by 尺码 set @s='select * from (select 颜色,数量,尺码 from 颜色表 a,尺寸表 b where a.id=b.id and a.pid=b.pid) a pivot (sum(数量) for 尺码 in ('+@s+')) c ' --print (@s) exec (@s)

[xxfvba]

declare @s varchar(max) set @s='' select @s=@s+','+ quotename(尺码)+'=sum(case when [尺码]='+quotename(尺码,'''')+ 'then 数量 else 0 end)' from 尺寸表 group by 尺码 set @s='select a.颜色'+@s+' from 颜色表 a,尺寸表 b where a.id=b.id and a.pid=b.pid group by a.颜色' --print @s exec(@s)

[唐诗三百首]

create table 颜色表
(Id varchar(20),颜色 varchar(10),pid int)

insert into 颜色表
 select '1288322773','蓝色',0 union all
 select '1288322773','黑色',1 union all
 select '1288322773','玫红',2

create table 尺寸表
(Id varchar(20),数量 int,尺码 varchar(10),pid int)

insert into 尺寸表
 select '1288322773',24,'L',0 union all
 select '1288322773',18,'XL',0 union all
 select '1288322773',21,'XXL',0 union all
 select '1288322773',15,'L',1 union all
 select '1288322773',18,'XL',1 union all
 select '1288322773',19,'XXL',1 union all
 select '1288322773',23,'L',2 union all
 select '1288322773',20,'XL',2 union all
 select '1288322773',27,'XXL',2


declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
            +'max(case when 尺码='''+尺码+''' then 数量 else 0 end) '''+尺码+''' '
 from (select distinct 尺码 from 尺寸表) t

exec('select b.颜色,'+@tsql
    +' from 尺寸表 a
       left join 颜色表 b on a.Id=b.Id and a.pid=b.pid
       group by b.颜色,b.pid
       order by b.pid ')

/*
颜色         L           XL          XXL
---------- ----------- ----------- -----------
蓝色         24          18          21
黑色         15          18          19
玫红         23          20          27

(3 row(s) affected)
*/

[KeepSayingNo]

  
Declare @sql varchar(max)  
    set @sql=STUFF((select ','+QUOTENAME(CONVERT(varchar(10),[尺码],120)) from 
           (select [数量],[尺码],[颜色] from 尺寸表 a
            inner join 颜色表 b
            on a.pid=b.pid) t group by [尺码] FOR XML PATH('')) ,1,1,'')  
    
    set @sql='select * from (select [数量],[尺码],[颜色] from 尺寸表 a
              inner join 颜色表 b
              on a.pid=b.pid) t  
              pivot (sum([数量])for [尺码] in ('+@sql+')) a '  
    exec(@sql)  

结果：