如何使用列作为在SQL中枢轴列的键

Question

我正试着把一个表旋转起来，并把一些子类别聚合起来。但是，当聚合时，子类别没有正确地连接起来。

示例表

| category | categorySub |
|----------|-------------|
| cat-1    | sub-1       |
| cat-1    | sub-2       |
| cat-1    | sub-3       |
| cat-2    | sub-4       |
| cat-2    | sub-5       |

实际输出

| category | categorySub                               |
|----------|-------------------------------------------|
| cat-1    | ["sub-1","sub-2","sub-3","sub-4","sub-5"] |
| cat-2    | ["sub-1","sub-2","sub-3","sub-4","sub-5"] |

期望输出

| category | categorySub               |
|----------|---------------------------|
| cat-1    | ["sub-1","sub-2","sub-3"] |
| cat-2    | ["sub-4","sub-5"]         |

查询

select 
[categoryMain] = [category],
[categorySub] = '["' + (select string_agg(categorySub, '","') from someTable where [categoryMain] = [category]) + '"]'
from someTable
group by [category]

如何引用类别或其别名来减少字符串聚合？

Answer 1

您可以在STRING_AGG本身中使用它。

DECLARE @T Table(
cat Varchar(max),
SUB varchar(max))

Insert into @T Values ('cat-1','sub-1')
Insert into @T Values ('cat-1','sub-2')
Insert into @T Values ('cat-1','sub-3')
Insert into @T Values ('cat-2','sub-4')
Insert into @T Values ('cat-2','sub-5')

SELECT CAT AS CATEGORYMAIN, '["' + STRING_AGG(SUB, '","')+ '"]' AS SUB 
FROM @T GROUP BY [CAT]

Answer 2

正如Martin建议的那样，我们可以直接使用string_agg，比如：

select 
[categoryMain] = [category],
[categorySub] = '["' + (select string_agg(categorySub, '","') + '"]'
from someTable
group by [category]