SQL 将行旋转到没有聚合的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15674373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot rows to columns without aggregate
提问by jlimited
Trying to figure how to write a dynamic pivot sql statement. Where TEST_NAMEcould have up to 12 different values (thus having 12 columns). Some of the VAL will be Int, Decimal, or Varchar data types. Most of the examples I have seen have some from of aggregate included. I am looking to for a straight value pivot.
试图弄清楚如何编写动态数据透视 sql 语句。其中TEST_NAME最多可以有 12 个不同的值（因此有 12 列）。某些 VAL 将是 Int、Decimal 或 Varchar 数据类型。我见过的大多数例子都包含了一些来自聚合的。我正在寻找一个直接的价值支点。
Source Table
╔═══════════╦══════╦═══════╗
║ TEST_NAME ║ SBNO ║ VAL ║
╠═══════════╬══════╬═══════╣
║ Test1 ║ 1 ║ 0.304 ║
║ Test1 ║ 2 ║ 0.31 ║
║ Test1 ║ 3 ║ 0.306 ║
║ Test2 ║ 1 ║ 2.3 ║
║ Test2 ║ 2 ║ 2.5 ║
║ Test2 ║ 3 ║ 2.4 ║
║ Test3 ║ 1 ║ PASS ║
║ Test3 ║ 2 ║ PASS ║
╚═══════════╩══════╩═══════╝
Desired Output
╔══════════════════════════╗
║ SBNO Test1 Test2 Test3 ║
╠══════════════════════════╣
║ 1 0.304 2.3 PASS ║
║ 2 0.31 2.5 PASS ║
║ 3 0.306 2.4 NULL ║
╚══════════════════════════╝
回答by Taryn
The PIVOTfunction requires an aggregation to get it to work. It appears that your VALcolumn is a varcharso you will have to use either the MAXor MINaggregate functions.
该PIVOT函数需要聚合才能使其工作。您的VAL列似乎是 a，varchar因此您必须使用 theMAX或MIN聚合函数。
If the number of tests is limited, then you can hard-code the values:
如果测试数量有限，那么您可以对这些值进行硬编码：
select sbno, Test1, Test2, Test3
from
(
select test_name, sbno, val
from yourtable
) d
pivot
(
max(val)
for test_name in (Test1, Test2, Test3)
) piv;
See SQL Fiddle with Demo.
In your OP, you stated that you will have an larger number of rows to turn into columns. If that is the case, then you can use dynamic SQL:
在您的 OP 中，您表示您将有更多的行变成列。如果是这种情况，那么您可以使用动态 SQL：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(TEST_NAME)
from yourtable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT sbno,' + @cols + '
from
(
select test_name, sbno, val
from yourtable
) x
pivot
(
max(val)
for test_name in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle with Demo.
Both versions will give the same result:
两个版本都会给出相同的结果：
| SBNO | TEST1 | TEST2 | TEST3 |
---------------------------------
| 1 | 0.304 | 2.3 | PASS |
| 2 | 0.31 | 2.5 | PASS |
| 3 | 0.306 | 2.4 | (null) |
回答by Samina
There is no any way to PIVOT without aggregating.
没有聚合就没有任何方法可以进行 PIVOT。
CREATE TABLE #table1
(
TEST_NAME VARCHAR(10),
SBNO VARCHAR(10),
VAL VARCHAR(10)
);
INSERT INTO #table1 (TEST_NAME, SBNO, VAL)
VALUES ('Test1' ,'1', '0.304'),
('Test1' ,'2', '0.31'),
('Test1' ,'3', '0.306'),
('Test2' ,'1', '2.3'),
('Test2' ,'2', '2.5'),
('Test2' ,'3', '2.4'),
('Test3' ,'1', 'PASS'),
('Test3' ,'2', 'PASS')
WITH T AS
(
SELECT SBNO, VAL, TEST_NAME
FROM #table1
)
SELECT *
FROM T
PIVOT (MAX(VAL) FOR TEST_NAME IN([Test1], [Test2], [Test3])) P
回答by pnuts
A workaround might be to ensure that the obligatory aggregation is only ever applied to a single record. In Excel for example the output could be:
一种解决方法可能是确保强制聚合只应用于单个记录。例如，在 Excel 中，输出可能是：
where Row Labels includes at the bottom a column of cells with unique index numbers.
其中 Row Labels 在底部包含一列具有唯一索引号的单元格。
