在SQL中使用类别值将行转置为列

发布于02月09日

在SQL中，有没有办法将类别值从行转置到列

示例

create table sample1 (
    Col1 nvarchar(max),
    [Values] nvarchar(max),
)

insert into sample1
values ('CatA','XCY'), ('CatB','XCY'), ('CatA','XC'), ('CatB','XC'), ('CatA','KJ'), ('CatA','KG'),('CatA','KFD'), ('CatB','KG')

select * from sample1(将给出低于值的值)

Col1    Values
CatA    XCY
CatB    XCY
CatA    XC
CatB    XC
CatA    KJ
CatA    KG
CatA    KFD
CatB    KG

预期yields

CatA   CatB
XCY    XCY
XC     XC
KJ     NULL
KG     KG
KFD    NULL

我try 了下面的方法，但没有得到


select *
from
(
  select [Values], Col1
  from sample1
) d
pivot
(
  max([Values])
  for Col1 in (CatA, CatB)
) piv;

推荐答案

这可以通过一个完整的连接来解决，它可以确保双方都可以获得它们的值，并使用NULL填充缺少的值:

create table sample1 (
Col1 nvarchar(max),
[Values] nvarchar(max),
)

insert into sample1
values ('CatA','XCY'), ('CatB','XCY'), ('CatA','XC'), ('CatB','XC'), ('CatA','KJ'), ('CatA','KG'),('CatA','KFD'), ('CatB','KG')

select a.[values] as catA, b.[values] as catB
from (
    select col1, [values]
    from sample1
    where col1 = 'CatA'
    ) a
full join (
    select col1, [values]
    from sample1
    where col1 = 'CatB'
    ) b
    ON b.[values] = a.[values]

输出: