我遇到的几乎所有类似问题都建议使用PIVOT,但我不明白如何在我的案例中使用它。
这是我的问题
SELECT
SUM(CONVERT(INT,H01)) as H01,
SUM(CONVERT(INT,H02)) as H02,
SUM(CONVERT(INT,H03)) as H03,
SUM(CONVERT(INT,H04)) as H04,
SUM(CONVERT(INT,H05)) as H05,
SUM(CONVERT(INT,H06)) as H06,
SUM(CONVERT(INT,H07)) as H07,
SUM(CONVERT(INT,H08)) as H08,
SUM(CONVERT(INT,H09)) as H09,
SUM(CONVERT(INT,H10)) as H10,
SUM(CONVERT(INT,H11)) as H11,
SUM(CONVERT(INT,H12)) as H12,
SUM(CONVERT(INT,H13)) as H13,
SUM(CONVERT(INT,H14)) as H14,
SUM(CONVERT(INT,H15)) as H15
FROM
IslemFisi
WHERE
(H01 = 1) OR (H02 = 1) OR (H03 = 1) OR (H04 = 1) OR
(H05 = 1) OR (H06 = 1) OR (H07 = 1) OR (H08 = 1) OR
(H09 = 1) OR (H10 = 1) OR (H11 = 1) OR (H12 = 1) OR
(H13 = 1) OR (H14 = 1) OR (H15 = 1)它返回这个结果集
+-------+------+------+------+-----+------+-----+------+-----+-----+------+-----+-----+------+-------+
| H01 | H02 | H03 | H04 | H05 | H06 | H07 | H08 | H09 | H10 | H11 | H12 | H13 | H14 | H15 |
+-------+------+------+------+-----+------+-----+------+-----+-----+------+-----+-----+------+-------+
| 11802 | 2239 | 1575 | 2230 | 833 | 3189 | 152 | 4234 | 617 | 428 | 2211 | 263 | 783 | 3954 | 15922 |
+-------+------+------+------+-----+------+-----+------+-----+-----+------+-----+-----+------+-------+我想把它放在一个单独的专栏中,但不知道怎么做。使用pivot需要一些聚合函数,但在这一点上我没有要聚合的东西。
发布于 2016-06-17 20:52:22
;WITH cte AS (
--put your query here
)
SELECT H, [VALUES]
FROM (
SELECT *
FROM cte) as p
UNPIVOT (
[VALUES] FOR H IN (H01, H02, H03, H04 ...etc)
) as unpvt输出:
H VALUES
H01 11802
H02 2239
....https://stackoverflow.com/questions/37881780
复制相似问题