当所有的柱子都是相同的,但只有一个的时候,我想把几个木条组合成一个。这就是一个例子:
ID | Name | CP | Job
-----------------------------
1 | Muse | 13 | Job1
1 | Muse | 13 | Job2
1 | Muse | 13 | Job3
2 | Tort | 51 | Job4
2 | Tort | 51 | Job5
我想要:
ID | Name | CP | Job
-----------------------------
1 | Muse | 13 | Job1, Job2, Job3
2 | Tort | 51 | Job4, Job5
我试图以这种方式使用STRING_AGG:
SELECT ID, Name, CP, STRING_AGG(Job, ',')
FROM myTable
GROUP BY ID, Name, CP, Job
ORDER BY ID
我读过这样的文章:https://database.guide/how-to-return-query-results-as-a-comma-separated-list-in-sql-server/
谢谢你的帮忙
发布于 2019-09-09 13:35:18
事实上,我自己才发现的.我不需要按职务分组:
SELECT ID, Name, CP, STRING_AGG(Job, ',')
FROM myTable
GROUP BY ID, Name, CP
ORDER BY ID
发布于 2019-09-09 20:18:01
只是一个有趣的观察和一些关于使用STRING_AGG的知识。如果您需要按任何特定顺序返回新职务列中的项,则可以利用WITHIN GROUP
。
-- Sample data with the inserts rearranged in random order
DECLARE @myTable TABLE
(
ID INT,
[Name] VARCHAR(100),
CP INT,
Job VARCHAR(100)
);
INSERT @myTable
VALUES
(1,'Muse',13,'Job3'),
(1,'Muse',13,'Job1'),
(1,'Muse',13,'Job2'),
(2,'Tort',51,'Job5'),
(2,'Tort',51,'Job4');
-- STRING_AGG ordering NOT guaranteed
SELECT ID, Name, CP, Job = STRING_AGG(Job, ',')
FROM @myTable
GROUP BY ID, Name, CP
ORDER BY ID;
-- STRING_AGG ordering IS guaranteed
SELECT ID, Name, CP, job = STRING_AGG(Job, ',') WITHIN GROUP (ORDER BY Job)
FROM @myTable
GROUP BY ID, Name, CP
ORDER BY ID;
无序STRING_AGG结果:
ID Name CP Job
----------- ----------- ----------- ------------------
1 Muse 13 Job3,Job1,Job2
2 Tort 51 Job5,Job4
无序STRING_AGG结果:
ID Name CP job
----------- ----------- ----------- ------------------
1 Muse 13 Job1,Job2,Job3
2 Tort 51 Job4,Job5
https://stackoverflow.com/questions/57855118
复制相似问题