将列数据当成列名并查询查询数据

一、需求

sqlserver使用存储过程将列数据当成列名并查询查询数据

二、模拟表

id	name	size	number
1	a	S	4
2	a	M	8
3	a	L	4
4	a	M	7
5	c	S	4
6	b	M	8
7	c	S	4
8	d	M	8
9	d	S	4
10	e	M	8
11	d	S	4
12	d	M	8

三、要实现的效果

id	name	S	M	L
1	a	4	15	4
2	b	0	8	0

四、思路

1. 先把Size全查询出来，作为列名，创建临时表

   1. 从表中读取size,并存入临时表

   	IF Object_Id('tempdb.dbo.##size_table') is not null  
   		drop table ##size_table  
   	select *
   	into ##size_table
   	from (
   		select distinct Size from table
   	) a
   

   2. 使用for xml path(‘’)将临时表数据以xml的形式读取并拼装成字符串

   	declare @size_columns varchar(max) = ''
   	set @size_columns = ( select '[' + Size + '] int,'
   			  from ##size_table 
   			  for xml path('')	
   			)
   

   3. 创建临时表

   	IF Object_Id('tempdb.dbo.##table') is not null  
   	drop table ##table  
   
   	declare @sql1 nvarchar(max) 
   	set @sql1= N'
     		create table ##table  
     		(  
   		   id int,  
   		   name varchar(10),'
   		   + @size_columns)'
     	exec( @sql1 )
   

2. 查询数据时，根据name分组查询，根据size的不同，计算number的和

   1. 读取临时表，拼装成size列名

   	declare @size_insert nvarchar(max)
   	set @size_insert = ( select ',[' + Size + ']' from ##size_table for xml path('') ) 
   

   2. 拼装成case方式，用于读取数据

   	declare @size_Pcs_Qty nvarchar(max)
   	set @size_Pcs_Qty = ( select ',sum(case when b.Size=''' + Size + ''' then Pcs_Qty else 0 end) as [' + Size + ']'
   			  from ##size_table 
   			  for xml path('')	
   			)
   

   3. 将数据插入临时表

   	declare @sql2 nvarchar(max)
   	set @sql2 = N'
   	insert ##table  
   	(  
   	 id, name' + @size_insert )  
   	select id, name' + @size_Pcs_Qty + 'from table'
   

3. 最后查询临时表数据

   	select * from ##table
   

注意事项

1. 临时表要使用##，因为#作用域只是存在于一个exec中，别的地方无法调用
2. 不可使用##，会影响数据库服务器性能