我正在尝试透视表并聚合一些子类别。但是，进行汇总时，子类别未正确连接。
示例表
| 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]
如何引用[类别]或其别名以减少字符串聚合？
您可以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]
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句