How to use GROUP BY to concatenate strings in SQL Server?

by

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[Note: This solution only works on SQL 2005 and later. Original question didn’t specify the version in use.]

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

More Related Contents:

  1. How to concatenate text from multiple rows into a single text string in SQL Server
  2. How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate]
  3. SQL NVARCHAR and VARCHAR Limits
  4. Concat groups in SQL Server [duplicate]
  5. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  6. SQL comma-separated row with Group By clause
  7. SQL, How to Concatenate results?
  8. How to return only the Date from a SQL Server DateTime datatype
  9. Convert Datetime column from UTC to local time in select statement
  10. How to implement LIMIT with SQL Server? [duplicate]
  11. Why is SQL Server losing a millisecond?
  12. converting Epoch timestamp to sql server(human readable format)
  13. What are Covering Indexes and Covered Queries in SQL Server?
  14. How do I split a delimited string so I can access individual items?
  15. Add a summary row with totals
  16. T-SQL split string based on delimiter
  17. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  18. Why use Select Top 100 Percent?
  19. How do I find duplicates across multiple columns?
  20. Calculate business hours between two dates
  21. SQL Server UNION – What is the default ORDER BY Behaviour
  22. How to get last 7 days data from current datetime to last 7 days in sql server
  23. How to check which locks are held on a table
  24. store arabic in SQL database
  25. What is the Null Character literal in TSQL?
  26. SQL Server : SUM() of multiple rows including where clauses
  27. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  28. SQL can I have a “conditionally unique” constraint on a table?
  29. Count consecutive duplicate values in SQL
  30. How to improve performance for datetime filtering in SQL Server?

Leave a Comment