如何在sql查询中将多列设置为行
如何在sql查询中将多列设置为行
提问于 2019-06-12 01:45:05
我想使用SQL Server将列转换为行。我已经尝试过取消透视，但没有足够的知识使它只能使一列
SELECT Unit,Excess_Reactivity ,Refuelling_required,MBP,MCP ,CPPF,Date FROM yourTable // select statement
//insert to check data
INSERT INTO yourTable
(Unit,Excess_Reactivity ,Refuelling_required,MBP ,MCP ,CPPF ,Date)
VALUES
('TAP-4','52','test1','12','96','35','2019-06-11 00:00:00.000')
INSERT INTO yourTable
(Unit,Excess_Reactivity ,Refuelling_required,MBP ,MCP ,CPPF ,Date)
VALUES
('TAP-2','52','test1','12','96','35','2019-06-11 00:00:00.000')
INSERT INTO yourTable
(Unit,Excess_Reactivity ,Refuelling_required,MBP ,MCP ,CPPF ,Date)
VALUES
('TAP-3','52','test1','12','96','35','2019-06-11 00:00:00.000')这是桌子
+--------+--------+---------+-----+-----+------+------------+
| UNit | excess | refuell | MBp | MCP | CPPF | DATE |
+--------+--------+---------+-----+-----+------+------------+
| TAPS-4 | 52 | test1 | 12 | 96 | 35 | 11/06/2019 |
| TAPS-3 | 52 | test1 | 13 | 96 | 35 | 11/06/2019 |
| TAPS-2 | 52 | test1 | 42 | 96 | 35 | 11/06/2019 |
| TAPS-1 | 52 | test1 | 18 | 96 | 35 | 11/06/2019 |
+--------+--------+---------+-----+-----+------+------------+我希望如下所示：
Values TAPS-4 TAPS-3 TAPS-2 TAPS-1
--------------------------------------
excess 52 52 52 52
MBp 12 13 42 18
MCP 96 96 96 96
CPPF 35 35 35 35回答 1
Stack Overflow用户
发布于 2019-06-12 02:24:41
- 首先你需要取消透视你的表，注意你需要将你的数据转换成相同的类型，因为每个数据都会进入相同的列。
- 然后你可以透视到你想要的结果。
- 如果需要，可以将其转换回原始数据类型。
WITH unpvt as (
SELECT Unit, Attribute, Vals
FROM ( SELECT Unit,
cast(excess as nvarchar(100)) as excess,
cast(MBp as nvarchar(100)) as MBp,
cast(MCP as nvarchar(100)) as MCP,
cast(CPPF as nvarchar(100)) as CPPF
FROM Table1) as p
UNPIVOT
(Vals FOR Attribute in (excess, MBp, MCP, CPPF)
) as unpvt
)
SELECT Attribute, [TAPS-4], [TAPS-3], [TAPS-2], [TAPS-1]
FROM ( SELECT Unit, Attribute, Vals
FROM unpvt) p
PIVOT (
MAX(Vals) FOR Unit IN
([TAPS-4], [TAPS-3], [TAPS-2], [TAPS-1])
) pvt输出
| Attribute | TAPS-4 | TAPS-3 | TAPS-2 | TAPS-1 |
|-----------|--------|--------|--------|--------|
| CPPF | 35 | 35 | 35 | 35 |
| excess | 52 | 52 | 52 | 52 |
| MBp | 12 | 13 | 42 | 18 |
| MCP | 96 | 96 | 96 | 96 |注意：在sql中尝试SELECT * FROM unpvt以查看中间结果。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56549162
复制相关文章
相似问题
腾讯云开发者
