sqlServer列转行

https://www.cnblogs.com/Rawls/p/11027413.html

使用PIVOT、EXEC关键 可以动态产生列
例子：

DECLARE @SqlSubject1 varchar(max)   /* 条件 */
SELECT @SqlSubject1 = STUFF((SELECT ',' + '[' + oc_country + ']' FROM (select distinct oc_country from stal2019 where ie_id='0') T FOR XML PATH('')),1,1,'')/* 当年 */
/* 输出的内容为‘1’，‘2’，‘3’ ，‘4’，stuff函数是替换第一位，for xml 是转为xml文件*/
DECLARE @SqlMONTH1 varchar(max) 
SELECT @SqlMONTH1 = STUFF((SELECT ',' + '[' + oc_country + '] AS m' + oc_country FROM (select distinct oc_country from stal2019 where ie_id='0') T FOR XML PATH('')),1,1,'')  /* 当月字段 */
DECLARE @SqlPIVOT1 varchar(max) 
SELECT @SqlPIVOT1='
	select * from 
	(select left(code_t,2) as code_t,sum(s_rmb) as s_rmb,oc_country from stal2019 group by left(code_t,2),oc_country  
	)d
	pivot(sum(d.s_rmb) for d.oc_country in(' + @SqlSubject1 + ')) curent_year 
	cross apply
		(
		 select ' + @SqlMONTH1 + ' from 
			(select * from 
			(select left(code_t,2) as code_t,sum(s_rmb) as s_rmb,oc_country from stal2019 group by left(code_t,2),oc_country
			)d 
			pivot(sum(d.s_rmb) for d.oc_country in(' + @SqlSubject1 + ')) t2) pre_year
			where curent_year.code_t = pre_year.code_t
		)t2
	'
	EXEC (@SqlPIVOT1)