~楓花雪岳~: [SQL] 字串欄位轉置

星期五, 10月 21, 2011

[SQL] 字串欄位轉置

利用尋找各班級前三名，來解釋字串欄位要如何轉置，在此不討論前三名是否有同分的情況。

-- 建立測試資料
DECLARE @Temp Table (class char(10),SdtName char(10),Score smallint)
INSERT INTO @Temp VALUES ('小叮噹班','大雄',10)
INSERT INTO @Temp VALUES ('小叮噹班','阿福',90)
INSERT INTO @Temp VALUES ('小叮噹班','技安',70)
INSERT INTO @Temp VALUES ('小叮噹班','宜靜',100)
INSERT INTO @Temp VALUES ('海賊王班','魯夫',20)
INSERT INTO @Temp VALUES ('海賊王班','娜美',90)
INSERT INTO @Temp VALUES ('海賊王班','騙人布',65)
INSERT INTO @Temp VALUES ('海賊王班','香吉士',30)
INSERT INTO @Temp VALUES ('海賊王班','索隆',20)
INSERT INTO @Temp VALUES ('網球王子班','不二',95)
INSERT INTO @Temp VALUES ('網球王子班','英二',75)
INSERT INTO @Temp VALUES ('網球王子班','手塚',99)
INSERT INTO @Temp VALUES ('網球王子班','越前',80)
INSERT INTO @Temp VALUES ('網球王子班','河村',20)

CASE 搭配 MAX()

轉置的第一個要點是必須要有一個基準欄位來辨別前三名，在此利用 ROW_NUMBER() 來產生此基準欄位。

SELECT * ,
       ROW_NUMBER() OVER (PARTITION BY class ORDER BY Score DESC) AS ROWNO
FROM @Temp

利用 CASE 語法，根據基準欄位來進行字串欄位轉置

SELECT
      T.class,
      T.SdtName,
      CASE WHEN ROWNO = 1 THEN T.SdtName ELSE NULL END AS Col1,
      CASE WHEN ROWNO = 2 THEN T.SdtName ELSE NULL END AS Col2,
      CASE WHEN ROWNO = 3 THEN T.SdtName ELSE NULL END AS Col3
FROM
  (
      SELECT * ,
             ROW_NUMBER() OVER (PARTITION BY class ORDER BY Score DESC) AS ROWNO
      FROM @Temp
  ) AS T

再利用 MAX() 會忽略 NULL 值的特性，把每各班級的前三名資料，整理成一筆

SELECT
      T.class AS '班級',
      MAX(CASE WHEN ROWNO = 1 THEN T.SdtName ELSE NULL END) AS '第一名',
      MAX(CASE WHEN ROWNO = 2 THEN T.SdtName ELSE NULL END) AS '第二名',
      MAX(CASE WHEN ROWNO = 3 THEN T.SdtName ELSE NULL END) AS '第三名'
FROM
  (
      SELECT * ,
             ROW_NUMBER() OVER (PARTITION BY class ORDER BY Score DESC) AS ROWNO
      FROM @Temp
  ) AS T
GROUP BY T.class

利用 PIVOT 進行轉置

利用 ROW_NUMBER() 來產生基準欄位，轉置才改用 PIVOT 來取代 CASE 和 MAX() 的搭配。

SELECT 
  Class AS [班級] , 
  [1] AS [第一名] ,
  [2] AS [第二名] ,
  [3] AS [第三名]
FROM
  (
    SELECT 
      Class , 
      SdtName ,
      ROW_NUMBER() OVER (PARTITION BY class ORDER BY Score DESC) AS ROWNO
    FROM @Temp
  ) AS P
PIVOT
  (
    MAX(SdtName) FOR ROWNO IN ([1],[2],[3])
  ) AS PV