当没有任何id时，使用unpivot在SQL Server 2012中的行

I have a table below:-

我有一张桌子： -

X_Q1 | X_Q2 | X_Q3 | X_Q4 | Y_Q1 | Y_Q2 | Y_Q3 | Y_Q4
---- | ---- | ---- | ---- | ---- | ---- | ---- | ----
4500 | 3400 | 4600 | 3456 | 3435 | 7643 | 3214 | 43434

Script:

脚本：

Create table #Temp
(
    X_Q1 int,
    X_Q2 int,
    X_Q3 int,
    X_Q4 int,
    Y_Q1 int,
    Y_Q2 int,
    Y_Q3 int,
    Y_Q4 int
    )

insert into #Temp values(4500,3400,4600,3456,3435,7643,3214,43434)

select * from #Temp
drop table #Temp

I want the output in below format : -

我希望输出格式如下： -

Code  |     X   |    Y
----  |   ----  |  ---- 
Q1    |   4500  |  3435
Q2    |   3400  |  7643
Q3    |   4600  |  3214
Q4    |   3456  |  43434

1 个解决方案

#1

PIVOT and UNPIVOT don't require an ID or other unique combination of columns. You can easily write:

PIVOT和UNPIVOT不需要ID或其他唯一的列组合。你可以轻松地写：

select code, value
from (
        select          
            X_Q1, X_Q2, X_Q3, X_Q4,
            Y_Q1, Y_Q2, Y_Q3, Y_Q4 
        from #temp
    ) p
    unpivot 
    (
        Value for code in 
        (
             X_Q1, X_Q2, X_Q3, X_Q4,
             Y_Q1, Y_Q2, Y_Q3, Y_Q4 
        )
    ) as upvt

to unpivot the data into :

将数据转换为：

code    value
X_Q1    4500
X_Q2    3400
X_Q3    4600
X_Q4    3456
Y_Q1    3435
Y_Q2    7643
Y_Q3    3214
Y_Q4    43434

Your desired output though requires parsing the code into two separate columns, and PIVOTing again to convert X and Y into columns. PIVOT always works with an aggregation function. In the trivial case where your table has only one row, this doesn't matter. Most likely though, you need an ID now to avoid aggregating multiple row values into one :

您需要的输出虽然需要将代码解析为两个单独的列，然后再次PIVOTing将X和Y转换为列。 PIVOT始终使用聚合函数。在你的表只有一行的简单情况下，这没关系。但最有可能的是，您现在需要一个ID以避免将多个行值聚合为一个：

with xy as (
    select 
        ID,
        left(code,1) as col, 
        SUBSTRING(code,3,10) as code, 
        value
    from (
            select          
                ROW_NUMBER() over (order by (select null)) as ID,
                X_Q1, X_Q2, X_Q3, X_Q4,
                Y_Q1, Y_Q2, Y_Q3, Y_Q4 
            from #temp
        ) p
        unpivot 
        (
            Value for code in 
            (
                 X_Q1, X_Q2, X_Q3, X_Q4,
                 Y_Q1, Y_Q2, Y_Q3, Y_Q4 
            )
        ) as upvt
)
select 
    ID,code, [X],[Y]
from xy
PIVOT( 
    sum(value)
    for col in ([X],[Y])
) as pvt
order by ID

The row ID is generated by ROW_NUMBER() over (order by (select null)). The xy CTE is used to split the code into a col field with X,Y values and a new code field.

行ID由ROW_NUMBER（）生成（order by（select null））。 xy CTE用于将代码拆分为具有X，Y值和新代码字段的col字段。

This result is PIVOTed again using X, Y as the new field names.

使用X，Y作为新字段名称再次对此结果进行PIVOT。

The result is:

结果是：

ID  code    X       Y
1   Q1      4500    3435
1   Q2      3400    7643
1   Q3      4600    3214
1   Q4      3456    43434

#1