select 쿼리 결과를 세로행 또는 가로행으로 변경하기

세로행 -> 가로행으로 변경

CREATE TABLE Sales (
    Salesperson VARCHAR(50),
    Region VARCHAR(50),
    Sales INT
);

INSERT INTO Sales (Salesperson, Region, Sales)
VALUES ('John', 'North', 100),
       ('John', 'South', 150),
       ('John', 'East', 75),
       ('Mary', 'North', 200),
       ('Mary', 'South', 50),
       ('Mary', 'East', 125);
       
   
SELECT Salesperson, [North], [South], [East]
FROM (
    SELECT Salesperson, Region, Sales
    FROM Sales
) AS SourceTable
PIVOT (
    SUM(Sales)
    FOR Region IN ([North], [South], [East])
) AS PivotTable;


Salesperson | North | South | East
------------|-------|-------|------
John        | 100   | 150   | 75
Mary        | 200   | 50    | 125

 

가로행 -> 세로행으로 변경

PersonID | FirstName | LastName | Age
-------------------------------------
1       | John      | Smith    | 35
2       | Jane      | Doe      | 28
3       | Bob       | Johnson  | 42


SELECT PersonID, Attribute, Value
FROM Person
UNPIVOT 
(
  Value FOR Attribute IN (FirstName, LastName, Age)
) AS unpvt;


PersonID | Attribute | Value
---------------------------
1       | FirstName | John
1       | LastName  | Smith
1       | Age       | 35
2       | FirstName | Jane
2       | LastName  | Doe
2       | Age       | 28
3       | FirstName | Bob
3       | LastName  | Johnson
3       | Age       | 42

 

추가 참조 사이트

https://goldswan.tistory.com/9