I can return the values ​​as a string so

SELECT Column1 + ', ' + Column2 + .............. FROM <anything>

but what if I don't know how many columns there will be? For example, if this is a crosstab with a column source specified?

  • one
    This option. First, the result is stored in the auxiliary table (select * into). Then dynamically build a query, retrieving data from information_schema. - msi

1 answer 1

Dynamic SQL, a list of columns take their views sys.columns:

 --Создаём табличку IF OBJECT_ID('TempTable', 'U') IS NOT NULL DROP TABLE TempTable; GO CREATE TABLE TempTable ( column1 VARCHAR(50), column2 INT, column3 DATETIME ) GO --Наполняем данными WITH CTE AS( SELECT 1 N UNION ALL SELECT N+1 FROM CTE WHERE N<10 ) INSERT TempTable SELECT 'STR No ' + CAST(N AS VARCHAR(11)), N, DATEADD(DD, N, CAST(GETDATE() AS DATE)) FROM CTE GO --Создаём текст запроса из системного представления sys.columns DECLARE @sql NVARCHAR(4000) = ( SELECT 'SELECT ' + STUFF(( SELECT '+'', ''+' + 'CAST([' + name + '] AS VARCHAR(max))' FROM sys.columns WHERE object_id = OBJECT_ID('TempTable', 'U') ORDER BY column_id FOR XML PATH('') ),1,6,'') + '[STR] FROM TempTable' ) SELECT @sql; --Сгенерированный текст запроса /* SELECT CAST([column1] AS VARCHAR(max))+', '+CAST([column2] AS VARCHAR(max))+', '+CAST([column3] AS VARCHAR(max))[STR] FROM TempTable */ --Запускаем EXEC(@sql) --Результат выполнения запроса /* STR STR No 1, 1, окт 25 2016 12:00AM STR No 2, 2, окт 26 2016 12:00AM STR No 3, 3, окт 27 2016 12:00AM STR No 4, 4, окт 28 2016 12:00AM STR No 5, 5, окт 29 2016 12:00AM STR No 6, 6, окт 30 2016 12:00AM STR No 7, 7, окт 31 2016 12:00AM STR No 8, 8, ноя 1 2016 12:00AM STR No 9, 9, ноя 2 2016 12:00AM STR No 10, 10, ноя 3 2016 12:00AM */

UPD As correctly suggested by msi, if the data is taken not from a table, but from an arbitrary query, then you can put everything in a temporary table. Those. sort of

 IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL DROP TABLE #TempTable; GO SELECT * INTO #TempTable FROM <Anything>

Then everything is the same.

 DECLARE @sql NVARCHAR(4000) = ( SELECT 'SELECT ' + STUFF(( SELECT '+'', ''+' + 'CAST([' + name + '] AS VARCHAR(max))' FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..#TempTable', 'U') ORDER BY column_id FOR XML PATH('') ),1,6,'') + '[STR] FROM #TempTable' ) SELECT @sql; --Сгенерированный текст запроса /* SELECT CAST([column1] AS VARCHAR(max))+', '+CAST([column2] AS VARCHAR(max))+', '+CAST([column3] AS VARCHAR(max))[STR] FROM #TempTable */ --Запускаем EXEC(@sql)