Merge row values into a CSV (a.k.a GROUP_CONCAT for SQL Server)

by

For each pair of EntityID, AttributeID use the XML path trick to generate the CSV

 SELECT
    M.EntityID, M.AttributeID,
    SUBSTRING(CAST(foo.bar AS varchar(8000)), 2, 7999) AS Options
FROM
    (
    SELECT DISTINCT EntityID, AttributeID
    FROM MyTable
    ) M
    CROSS APPLY
    (
    SELECT
        ',' + OptionText
    FROM
        MyTable M2
    WHERE
        M.EntityID = M2.EntityID AND M.AttributeID= M2.AttributeID
    FOR XML PATH ('')
    ) foo(bar)

More Related Contents:

  1. Get the mentioned output by SQL query
  2. I need help formatting inner join command in SQL query
  3. Calculate a Running Total in SQL Server
  4. LIMIT 10..20 in SQL Server
  5. SQL transpose full table
  6. How to Create a real one-to-one relationship in SQL Server
  7. Select statement to find duplicates on certain fields
  8. Getting only Month and Year from SQL DATE
  9. How to fetch the row count for all tables in a SQL SERVER database [duplicate]
  10. WHERE Clause vs ON when using JOIN
  11. Using a Variable in OPENROWSET Query
  12. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  13. Does MS SQL Server’s “between” include the range boundaries?
  14. Check for file exists or not in sql server?
  15. How to get the first and last date of the current year?
  16. Split one column value into multiple column values
  17. SQL Server PIVOT perhaps?
  18. Why execute stored procedures is faster than SQL query from a script?
  19. DISTINCT for only one column
  20. How do I create and query linked database servers in SQL Server?
  21. Add emoji / emoticon to SQL Server table
  22. Perform regex (replace) in an SQL query
  23. What is the internal representation of datetime in sql server?
  24. Eliminate and reduce overlapping date ranges
  25. SELECT max(x) is returning null; how can I make it return 0?
  26. How to return default value from SQL query
  27. SQL INSERT INTO from multiple tables
  28. Group consecutive rows of same value using time spans
  29. Conditional aggregation performance
  30. SQL add filter only if a variable is not null

Leave a Comment