如何部分转置 SQL 表格而不使用聚合函数

Question

我需要你们专业的建议来部分转置这个MS SQL表：

| Term | Listening | Reading | Speaking | Writing |
| AC1  | 12        | 15      | 18       | 13      |
| AC2  | 17        | 14      | 9        | 12      |

转化为：

| Term | Comp      | Score |
| AC1  | Listening | 12    |
| AC1  | Reading   | 15    |
| AC1  | Speaking  | 18    |
| AC1  | Writing   | 13    |
| AC2  | Listening | 17    |
| AC2  | Reading   | 14    |
| AC2  | Speaking  | 9     |
| AC2  | Writing   | 12    |

如果您能提供建议，将不胜感激。先在这里谢过了。

- Bobbit

---

有成千上万的Pivot和UnPivot示例，可以在Google上搜索它们。 - ahmed abdelqader

Answer 1

你需要的是“UNPIVOT”函数：UNPIVOT。

SELECT Term, Comp, Score
FROM mytable
UNPIVOT
   (Score FOR Comp IN 
      ([Listening], [Reading], [Speaking], [Writing])
)AS unpvt;

- Giorgos Betsos

---

谢谢，Giorgos Betsos。这太棒了！ - Bobbit

Answer 2

SELECT term,comp,trems
FROM 
   (SELECT *
   FROM #b) p
UNPIVOT
   (trems FOR comp IN 
      (Listening, Reading, Speaking, Writing)
)AS unpvt;
GO

输出

term    comp    trems
AC1 Listening   12
AC1 Reading     15
AC1 Speaking    18
AC1 Writing     13
AC2 Listening   17
AC2 Reading     14
AC2 Speaking    9
AC2 Writing     12

或者

 SELECT
    term,comp,trems
    FROM #b
    CROSS APPLY (
          Values
             ('Listening' , Listening),
             ('Reading' , Reading),
             ('Speaking', Speaking) ,
             ('Writing', Writing)
      ) as CA (comp, trems)

   output


term    comp    trems
AC1 Listening   12
AC1 Reading     15
AC1 Speaking    18
AC1 Writing     13
AC2 Listening   17
AC2 Reading     14
AC2 Speaking    9
AC2 Writing     12

- Chanukya

Answer 3

您只需要使用UNPIVOT来将列转换为行。

SELECT
    UNPVTBL.Term
    , UNPVTBL.Comp
    , UNPVTBL.Score
FROM Table
UNPIVOT
(Score FOR Comp IN (Listening, Reading, Speaking, Writing) ) UNPVTBL
ORDER BY UNPVTBL.Term

- Radu Gheorghiu