如何在SQL中将列值作为列标题进行转置?

Question

>有没有办法在SQL中转置值？例

A    Units  Cat
Asd    4    Type1 
fsd    3    Type1 
Asd    2    Type2
gfd    5    Type2

预期输出

A   TotalUnits  Type1      Type2
Asd   6           4           2
fsd   3           3 
gfd   5                       5

Answer 1

我稍微改变了@Lajos答案

select t.A,sum(t.Units) as TotalUnits,case when sum(t.T1)=0 then '' 
else cast(sum(t.T1) As varchar) end as Type1,
case when sum(t.T2)=0 then '' else cast(sum(t.T2) as varchar) end as Type2 from
(select A,
Units,
case
when Cat = 'Type1' then Units
else 0
end as T1,
case
when Cat = 'Type2' then Units
else 0
end as T2
from yourtable) t group by t.A;

在此处查看演示 演示小提琴

Answer 2

是的，有。如果我们假设我们已经知道Cat值是什么，那么这样做就足够了：

select A,
Units,
case
when Cat = 'Type1' then Units
else 0
end as T1,
case
when Cat = 'Type2' then Units
else 0
end as T2
from yourtable;

上面的查询缺少聚合，让我们也这样做：

select A, sum(Units) as TotalUnits, sum(T1) as Type1, sum(T2) as Type2
from
(select A,
Units,
case
when Cat = 'Type1' then Units
else 0
end as Type1,
case
when Cat = 'Type2' then Units
else 0
end
from yourtable) t;
group by A;

如果您确实想要问题中所示总和为 0 的空白空间，那么您可以分别使用 case，对于 Type1 和 Type2 的值，本着最初定义的相同精神。

但是，您不一定知道Cat的可能值是什么。为了应对这种情况，您需要加载其distinct值并动态构建查询。