긍정적 사고, 음식의 절제, 규칙적인 운동

PIVOT

 - 행과 열 변환

SELECT 컬럼명 FROM 테이블명
PIVOT([집계 함수]([집계 컬럼명]) FOR [대상 컬럼명] IN ([컬럼이 될 값])) AS 피벗테이블명


WITH A AS
(
    SELECT '1학년' AS '학년', '1반' AS '반', 100 AS '점수'
    UNION ALL SELECT '1학년', '2반', 30
    UNION ALL SELECT '2학년', '1반', 80
    UNION ALL SELECT '2학년', '2반', 90
)
SELECT * FROM A
PIVOT(SUM(점수) FOR 반 IN ([1반],[2반])) AS PVT

UNPIVOT

 - PIVOT의 반대

SELECT 컬럼명 FROM 테이블명
UNPIVOT([집계될 데이터의 컬럼명] FOR [UNPIVOT할 컬럼 데이터의 컬럼명] IN ([UNPIVOT할 컬럼명])) AS 언피벗테이블명


WITH A AS
(
    SELECT '1학년' AS '학년', 100 AS '1반', 30 AS '2반'
    UNION ALL SELECT '2학년', 80, 90
)
SELECT 학년, 반, 점수 FROM A
UNPIVOT(점수 FOR 반 IN ([1반], [2반]))AS UNPVT

PIVOT SUM FOR IN PIVOT_TABLE

PIVOT을 이용하여 세로를 가로로 변환 방법(행 열 변환) 

SELECT Student  
, isnull(CAST([Mathematics] AS varchar), '' ) [Mathematics]
, isnull(CAST([Science] AS varchar), '' ) [Science]
, isnull(CAST([Geography] AS varchar), '' ) [Geography]
-- , * 
FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM #TEMP_TABLE_Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Mathematics],
    [Science],
    [Geography]
  )
) AS PivotTable;

IF OBJECT_ID('tempdb..#TEMP_TABLE_Grades') --임시 테이블 남아있을시 삭제 
  IS NOT NULL DROP TABLE #TEMP_TABLE_Grades;
 

CREATE TABLE #TEMP_TABLE_Grades(
  [Student] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Marks]   INT
);
 
INSERT INTO #TEMP_TABLE_Grades VALUES 
('Jacob','Mathematics',100),
('Jacob','Science',95),
('Jacob','Geography',90),
('Jacob2','Mathematics',''),
('Jacob2','Science',''),
('Amilee','Mathematics',90),
('Amilee','Science',90),
('Amilee','Geography',100);
 
SELECT * FROM (
  SELECT
    [Student],
    [Subject],
    [Marks]
  FROM #TEMP_TABLE_Grades
) StudentResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Mathematics],
    [Science],
    [Geography]
  )
) AS PivotTable