首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将单行结果转置为单列结果

问将单行结果转置为单列结果
EN

Stack Overflow用户
提问于 2016-06-17 20:37:19
回答 1查看 34关注 0票数 0

我遇到的几乎所有类似问题都建议使用PIVOT,但我不明白如何在我的案例中使用它。

这是我的问题

代码语言:javascript
复制
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)

它返回这个结果集

代码语言:javascript
复制
+-------+------+------+------+-----+------+-----+------+-----+-----+------+-----+-----+------+-------+
|  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需要一些聚合函数,但在这一点上我没有要聚合的东西。

EN

回答 1

Stack Overflow用户

发布于 2016-06-17 20:52:22

代码语言:javascript
复制
;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

输出:

代码语言:javascript
复制
H   VALUES
H01 11802
H02 2239
....
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/37881780

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档