oracle和sqlserver行转列

行转列效果图

sql语句如下：

-- Oracle行转列 
SELECT 
 	A.ORG_NAME,A.PAY_TYPE, 
 	listagg(A.YEAR_MONTH,',') within GROUP(order BY A.YEAR_MONTH) YEAR_MONTH_ARR, 
 	listagg(A.MON_SUM_AMOUNT,',') within GROUP(order BY A.MON_SUM_AMOUNT) MON_SUM_AMOUNT_ARR,
 FROM 
 ( 
 SELECT  
 	B.ORG_NAME,B.PAY_TYPE,to_char(B.PAY_DATE,'YYYY') YEAR_STR, 
 	to_char(B.PAY_DATE,'YYYYMM') YEAR_MONTH, 
 	SUM(B.AMOUNT) MON_SUM_AMOUNT 
 FROM T_test B 
 where 1=1 
 GROUP BY B.ORG_NAME,B.PAY_TYPE,to_char(B.PAY_DATE,'YYYYMM'),to_char(B.PAY_DATE,'YYYY') 
 ORDER BY B.ORG_NAME,B.PAY_TYPE,to_char(B.PAY_DATE,'YYYYMM'),to_char(B.PAY_DATE,'YYYY') 
 ) A 
 GROUP BY A.ORG_NAME,A.PAY_TYPE 
 ORDER BY A.ORG_NAME,A.PAY_TYPE 

--sqlserver 行转列
WITH TABLE_TMP AS (
	SELECT
		A.ORG_NAME,
		A.PAY_TYPE,
		CONVERT (VARCHAR(10), A.YEAR_MONTH) YEAR_MONTH,
		CONVERT (VARCHAR (30),A.MON_SUM_AMOUNT) MON_SUM_AMOUNT
	FROM
		(
			SELECT
				B.ORG_NAME,
				B.PAY_TYPE,
				YEAR (B.PAY_DATE) YEAR_STR,
				CONVERT (VARCHAR(6), B.PAY_DATE, 112) YEAR_MONTH,
				SUM (
					CAST (B.AMOUNT AS DECIMAL(18, 2))
				) MON_SUM_AMOUNT
			FROM
				T_DWH_OVERSEAS_PAYMENT B
			WHERE
				1 = 1
			AND B.ORG_NAME IN ('MBCL')
			AND (
				YEAR (B.PAY_DATE) >= '2018'
				AND YEAR (B.PAY_DATE) <= '2018'
			)
			GROUP BY
				B.ORG_NAME,
				B.PAY_TYPE,
				YEAR (B.PAY_DATE),
				CONVERT (VARCHAR(6), B.PAY_DATE, 112)
		) A
)
SELECT
	A.ORG_NAME,
	A.PAY_TYPE,
	YEAR_MONTH_ARR = STUFF(
		(
			SELECT
				',' + T2.YEAR_MONTH
			FROM
				TABLE_TMP T2
			WHERE
				A.ORG_NAME = T2.ORG_NAME
			AND (
				(
					T2.PAY_TYPE IS NOT NULL
					AND A.PAY_TYPE = T2.PAY_TYPE
				)
				OR T2.PAY_TYPE IS NULL
				AND A.PAY_TYPE IS NULL
			) FOR XML PATH ('')
		),
		1,
		1,
		''
	),
	MON_SUM_AMOUNT_ARR = STUFF(
		(
			SELECT
				',' + T2.MON_SUM_AMOUNT
			FROM
				TABLE_TMP T2
			WHERE
				A.ORG_NAME = T2.ORG_NAME
			AND (
				(
					T2.PAY_TYPE IS NOT NULL
					AND A.PAY_TYPE = T2.PAY_TYPE
				)
				OR T2.PAY_TYPE IS NULL
				AND A.PAY_TYPE IS NULL
			) FOR XML PATH ('')
		),
		1,
		1,
		''
	)
FROM
	TABLE_TMP A
GROUP BY
	A.ORG_NAME,
	A.PAY_TYPE