我如何得到:
id Name Value
1 A 4
1 B 8
2 C 9
to
id Column
1 A:4, B:8
2 C:9
我如何得到:
id Name Value
1 A 4
1 B 8
2 C 9
to
id Column
1 A:4, B:8
2 C:9
当前回答
使用Replace函数和FOR JSON PATH
SELECT T3.DEPT, REPLACE(REPLACE(T3.ENAME,'{"ENAME":"',''),'"}','') AS ENAME_LIST
FROM (
SELECT DEPT, (SELECT ENAME AS [ENAME]
FROM EMPLOYEE T2
WHERE T2.DEPT=T1.DEPT
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ENAME
FROM EMPLOYEE T1
GROUP BY DEPT) T3
有关示例数据和更多方法,请点击这里
其他回答
不需要光标…while循环就足够了。
------------------------------
-- Setup
------------------------------
DECLARE @Source TABLE
(
id int,
Name varchar(30),
Value int
)
DECLARE @Target TABLE
(
id int,
Result varchar(max)
)
INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9
------------------------------
-- Technique
------------------------------
INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id
DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)
WHILE @id is not null
BEGIN
SET @Result = null
SELECT @Result =
CASE
WHEN @Result is null
THEN ''
ELSE @Result + ', '
END + s.Name + ':' + convert(varchar(30),s.Value)
FROM @Source s
WHERE id = @id
UPDATE @Target
SET Result = @Result
WHERE id = @id
SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END
SELECT *
FROM @Target
八年后……Microsoft SQL Server vNext数据库引擎最终增强了Transact-SQL,以直接支持分组字符串连接。社区技术预览版本1.0添加了STRING_AGG函数,CTP 1.1为STRING_AGG函数添加了WITHIN GROUP子句。
参考:https://msdn.microsoft.com/en-us/library/mt775028.aspx
我使用了这种方法,可能更容易掌握。获取一个根元素,然后连接到具有相同ID但不是“正式”名称的选项
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 '%,%'
使用XML路径不会完美地连接你可能期望…它会将“&”替换为“&”,也会混淆<”和“>” ...也许还有其他一些事情,不确定……但是你可以试试这个
我想到了一个变通办法…您需要替换:
FOR XML PATH('')
)
:
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')
...或者NVARCHAR(MAX)如果这是你使用的。
为什么SQL没有一个串联聚合函数?这是PITA。
使用Stuff和for xml路径操作符将行连接到字符串:Group By两列——>
CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',5)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
-- retrieve each unique id and name columns and concatonate the values into one column
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES EACH APPLICATION : VALUE SET
FROM #YourTable
WHERE (ID = Results.ID and Name = results.[name] )
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
SELECT
[ID],[Name] , --these are acting as the group by clause
STUFF((
SELECT ', '+ CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES THE VALUES FOR EACH ID NAME COMBINATION
FROM #YourTable
WHERE (ID = Results.ID and Name = results.[name] )
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID, name
DROP TABLE #YourTable