Simulating group_concat MySQL function in Microsoft SQL Server 2005?

by

No REAL easy way to do this. Lots of ideas out there, though.

Best one I’ve found:

SELECT table_name, LEFT(column_names , LEN(column_names )-1) AS column_names
FROM information_schema.columns AS extern
CROSS APPLY
(
    SELECT column_name + ','
    FROM information_schema.columns AS intern
    WHERE extern.table_name = intern.table_name
    FOR XML PATH('')
) pre_trimmed (column_names)
GROUP BY table_name, column_names;

Or a version that works correctly if the data might contain characters such as <

WITH extern
     AS (SELECT DISTINCT table_name
         FROM   INFORMATION_SCHEMA.COLUMNS)
SELECT table_name,
       LEFT(y.column_names, LEN(y.column_names) - 1) AS column_names
FROM   extern
       CROSS APPLY (SELECT column_name + ','
                    FROM   INFORMATION_SCHEMA.COLUMNS AS intern
                    WHERE  extern.table_name = intern.table_name
                    FOR XML PATH(''), TYPE) x (column_names)
       CROSS APPLY (SELECT x.column_names.value('.', 'NVARCHAR(MAX)')) y(column_names)

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. SQL update query using joins
  4. Referring to a Column Alias in a WHERE Clause
  5. SQL query to select dates between two dates
  6. Convert Month Number to Month Name Function in SQL
  7. How to avoid the “divide by zero” error in SQL?
  8. Selecting COUNT(*) with DISTINCT
  9. Querying data by joining two tables in two database on different servers
  10. Is there any difference between IS NULL and =NULL
  11. Generate a resultset of incrementing dates in TSQL
  12. Real life example, when to use OUTER / CROSS APPLY in SQL
  13. Getting result of dynamic SQL into a variable for sql-server
  14. How to get difference between two rows for a column field?
  15. Better techniques for trimming leading zeros in SQL Server?
  16. SQL statement to select all rows from previous day
  17. What’s the best way to select the minimum value from several columns?
  18. How to insert a blob into a database using sql server management studio
  19. Best way to do nested case statement logic in SQL Server
  20. How to check which locks are held on a table
  21. Produce DISTINCT values in STRING_AGG
  22. Execution sequence of Group By, Having and Where clause in SQL Server?
  23. How to find sum of multiple columns in a table in SQL Server 2005?
  24. SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE
  25. Is it possible to execute a text file from SQL query?
  26. Query times out from web app but runs fine from management studio
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. SQL Sort Order with Null values last
  29. Insert all values of a table into another table in SQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment