我该如何获得:
id Name Value
1 A 4
1 B 8
2 C 9
id Column
1 A:4, B:8
2 C:9
我该如何获得:
id Name Value
1 A 4
1 B 8
2 C 9
id Column
1 A:4, B:8
2 C:9
我使用了这种方法,可能更容易理解。获取根元素,然后将具有相同ID但不是“official”名称的任何项连接到choices中。
Declare @IdxList as Table(id int, choices varchar(max),AisName varchar(255))
Insert into @IdxLIst(id,choices,AisName)
Select IdxId,''''+Max(Title)+'''',Max(Title) From [dbo].[dta_Alias]
where IdxId is not null group by IdxId
Update @IdxLIst
set choices=choices +','''+Title+''''
From @IdxLIst JOIN [dta_Alias] ON id=IdxId And Title <> AisName
where IdxId is not null
Select * from @IdxList where choices like '%,%'
另一个没有垃圾的例子:",TYPE).value('(./text())[1]','VARCHAR(MAX)')"
WITH t AS (
SELECT 1 n, 1 g, 1 v
UNION ALL
SELECT 2 n, 1 g, 2 v
UNION ALL
SELECT 3 n, 2 g, 3 v
)
SELECT g
, STUFF (
(
SELECT ', ' + CAST(v AS VARCHAR(MAX))
FROM t sub_t
WHERE sub_t.g = main_t.g
FOR XML PATH('')
)
, 1, 2, ''
) cg
FROM t main_t
GROUP BY g
输入输出是
************************* -> *********************
* n * g * v * * g * cg *
* - * - * - * * - * - *
* 1 * 1 * 1 * * 1 * 1, 2 *
* 2 * 1 * 2 * * 2 * 3 *
* 3 * 2 * 3 * *********************
*************************
create table test_string_agg(
id int,
str_text varchar(100)
)
-- 插入
insert into test_string_agg
values (1,'Text1'),(1,'Text2'),(1,'Text3'), (2,'Text4')
--请求
select id, STRING_AGG(str_text, ';') as t_string_agg from test_string_agg
group by id
-------------------------------
| id | t_string_agg
----------------------------------
| 1 | Text1;Text2;Text3
---------------------------------
| 2 | Text4
-----------------------------------
GROUP_CONCAT()
聚合函数轻松解决这种问题,但在Microsoft SQL Server上解决这个问题则比较麻烦。需要帮助的话可以参考以下Stack Overflow的问题: "如何基于关系获取一个记录的多个记录?" - Bill Karwin