我如何得到:
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
当前回答
这只是Kevin Fairchild的文章的补充(顺便说一句,非常聪明)。我本来会把它作为一个评论,但我还没有足够的分数:)
我将这个想法用于我正在工作的视图,然而我正在连接的项目包含空间。因此,我稍微修改了代码,不再使用空格作为分隔符。
再次感谢你酷炫的解决办法,凯文!
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 (2, 'C', 9)
SELECT [ID],
REPLACE(REPLACE(REPLACE(
(SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A
FROM #YourTable
WHERE ( ID = Results.ID )
FOR XML PATH (''))
, '</A><A>', ', ')
,'<A>','')
,'</A>','') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
其他回答
如果group by只包含一个项目,您可以通过以下方式显著提高性能:
SELECT
[ID],
CASE WHEN MAX( [Name]) = MIN( [Name]) THEN
MAX( [Name]) NameValues
ELSE
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
END
FROM #YourTable Results
GROUP BY ID
如果你启用了clr,你可以使用GitHub中的Group_Concat库
不需要游标,WHILE循环或用户定义函数。
只需要创造性地使用FOR XML和PATH。
[注意:此解决方案仅适用于SQL 2005及更高版本。原来的问题没有指定使用的版本。
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 (2,'C',9)
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
使用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