How Can I Concatenate Strings in SQL Server Using GROUP BY?

Use the group by connecting string in SQL Server

When processing the string connection in SQL Server,

clause is a powerful tool. Based on a public field, you can effectively combine the relevant string into a single connection result.

GROUP BY Let's take a look at a scene:

You have a table containing ID, name, and value columns. You want to connect all rows with the same ID to a single line. The line contains columns called Column, which contains a connected string with format: value: value.

For this reason, you can use

and

and FOR XML clause: PATH GROUP BY

In this query:

<code class="language-sql">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</code>

Sub -query uses

to create the XML representation form of the name and value column, which is separated by a comma.
• FOR XML PATH('') The string specified by the clause should be regarded as a single value.
• Function deletes the comma and spaces beginning with the connection string. PATH('')
Use the clause according to the ID of the ID.
• STUFF
As a result, you will get the following output:
• GROUP BY

The above is the detailed content of How Can I Concatenate Strings in SQL Server Using GROUP BY?. For more information, please follow other related articles on the PHP Chinese website!