SQL Cách chuyển từ cột sang dòng

Cách pivot (chuyển từ dòng chuyển thành cột)

1. Tham khảo ở link sau:

https://forum.sapb1.vn/community/saphana/cach-pivot-ket-qua-truy-van-trong-sap-hana/#post-154

2. Sử dụng hàm Pivot trong SQL

SELECT 'Actual' 'Indicator',S1.Year, S1.SalesEmployee, 
ISNULL([1],0) as 'Jan',
ISNULL([2],0) as 'Feb',
ISNULL([3],0) as 'Mar',
ISNULL([4],0) as 'Apr',
ISNULL([5],0) as 'May',
ISNULL([6],0) as 'Jun',
ISNULL([7],0) as 'Jul',
ISNULL([8],0) as 'Aug',
ISNULL([9],0) as 'Sep',
ISNULL([10],0) as 'Oct',
ISNULL([11],0) as 'Nov',
ISNULL([12],0) as 'Dec'
FROM
(
select Year(T0.DocDate) 'Year', month(T0.DocDate) 'Month', T2.SlpName 'SalesEmployee', T1.LineTotal 'Amount'
from OINV T0 join INV1 T1 on T0.DocEntry=T1.DocEntry
left join OSLP T2 on T0.SlpCode=T2.SlpCode
where T0.CANCELED='N'
union
select Year(T0.DocDate) 'Year', month(T0.DocDate) 'Month', T2.SlpName 'SalesEmployee', -T1.LineTotal 'Amount'
from ORIN T0 join RIN1 T1 on T0.DocEntry=T1.DocEntry
left join OSLP T2 on T0.SlpCode=T2.SlpCode
where T0.CANCELED='N'
) S0
PIVOT (SUM(Amount) FOR month IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) S1

Group by S1.Year, S1.SalesEmployee, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
Order by S1.Year Asc, S1.SalesEmployee asc

Cách unpivot (từ cột chuyển thành dòng)

select * from [@ZS_KPIS_1]
--convert from column to row
select DocEntry, U_SECode, U_SEName, '1' as 'Month', U_Jan as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '2' as 'Month', U_Feb as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '3' as 'Month', U_Mar as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '4' as 'Month', U_Apr as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '5' as 'Month', U_May as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '6' as 'Month', U_Jun as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '7' as 'Month', U_Jul as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '8' as 'Month', U_Aug as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '9' as 'Month', U_Sap as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '10' as 'Month', U_Oct as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '11' as 'Month', U_Nov as 'Amount' from [@ZS_KPIS_1]
union
select DocEntry, U_SECode, U_SEName, '12' as 'Month', U_Dec as 'Amount' from [@ZS_KPIS_1]

Minh họa: