将行旋转到没有聚合的列 [英] Pivot rows to columns without aggregate

问题描述

试图弄清楚如何编写动态枢轴sql语句.其中，TEST_NAME最多可以具有12个不同的值(因此具有12列).某些VAL将为Int，Decimal或Varchar数据类型.我看到的大多数示例都包含一些汇总.我正在寻找一个直接的价值枢纽.

Trying to figure how to write a dynamic pivot sql statement. Where TEST_NAME could 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.

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  ║
╚══════════════════════════╝

推荐答案

PIVOT函数需要进行汇总才能使其正常工作.看来您的VAL列是varchar，所以您将不得不使用MAX或MIN聚合函数.

The PIVOT function requires an aggregation to get it to work. It appears that your VAL column is a varchar so you will have to use either the MAX or MIN aggregate functions.

如果测试数量有限，则可以对值进行硬编码:

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;

请参见带有演示的SQL小提琴.

在您的OP中，您说过将有更多的行变成列.如果是这种情况，那么您可以使用动态SQL:

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:

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)

请参见带演示的SQL小提琴.

两个版本都将得到相同的结果:

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) |

这篇关于将行旋转到没有聚合的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！