Convert any query result to string

Question

Using Sql Server 2005 and tsql, I am looking for a way to convert the result of any query (e.g. select * from Person.Address ) into a large string, such as:

 AddressID AddressLine1 AddressLine2 1 1970 Napa Ct. NULL 2 9833 Mt. Dias Blv. NULL 3 7484 Roundtree Drive NULL 4 9539 Glenside Dr NULL 5 1226 Shoe St. NULL 

Can the cursor be in the know? I would like the mechanism to be general so that it can convert the result of any request into a string.

Answer 1

Try this option -

Solution No. 1 (works with any table structure):

Query:

 SET NOCOUNT ON; DECLARE @SQL NVARCHAR(MAX) , @TableName SYSNAME = 'Person.Address' SELECT @SQL = 'SELECT STUFF((SELECT CHAR(10) + ' + STUFF(( SELECT [text()] = CHAR(10) + '+ CAST(' + c.name + ' AS CHAR(' + CAST( CASE WHEN c.max_length = -1 OR TYPE_NAME(c.system_type_id) = 'uniqueidentifier' THEN 150 ELSE (c.max_length / 2) + 5 END AS VARCHAR(10)) + '))' FROM sys.columns c WHERE c.[object_id] = OBJECT_ID(@TableName) AND c.user_type_id = c.system_type_id FOR XML PATH('')), 1, 2, ' ') + ' FROM ' + @TableName + ' FOR XML PATH('''')), 1, 1, '''')' DECLARE @temp TABLE (t VARCHAR(MAX)) INSERT INTO @temp EXEC sys.sp_executesql @SQL DECLARE @SQL2 VARCHAR(MAX) SELECT @SQL2 = t FROM @temp PRINT @SQL2 

Output:

 66 4775 Kentucky Dr. Unit E Monroe 79 98272 462876DD-B30C-4E9B-A0AD-67A0E14986C6 Jun 27 20 83 5379 Treasure Island Way # 14 Duvall 79 98019 F229A37B-C8D9-4E4A-99BB-4D12715A2F5E Mar 8 20 101 3243 Buckingham Dr. # 207 Seattle 79 98104 0F1F79AB-54A1-4C6C-BEB7-E476E8521599 Mar 19 20 102 3029 Pastime Dr # 2 Seattle 79 98104 5E77CA8D-72D4-4572-8F88-1A1A5A7969F0 Feb 5 20 158 6058 Hill Street # 4 Bellevue 79 98004 CACF831D-22DD-4E2B-A377-E11A89D2D526 Jan 19 20 170 7902 Grammercy Lane Unit A Bellevue 79 98004 5C44745C-D11A-4F11-B9BC-345D4E064780 Jan 5 20 179 771 Northridge Drive # 495 Bellevue 79 98004 335645F3-B5D5-4B96-BAC1-22267CDDF8D2 Jul 9 20 260 3884 Beauty Street # 14 Gold Bar 79 98251 9F9D6796-675A-4619-8F37-EDA885DB931E Mar 8 20 266 7691 Benedict Ct. # 141 Issaquah 79 98027 980F8C50-DDC1-414C-9230-4F0CECA16DB3 Mar 3 20 282 5980 Icicle Circle Unit H Renton 79 98055 F32EB450-1220-461A-9CC5-3366ED213517 Feb 17 20 323 7651 Smiling Tree Court Space 55 Los Angeles 9 90012 2DDF992E-B883-4476-956A-EF44AE9F020C Jan 24 20 331 419 River Ash Court #9 Lakewood 37 63301 69783E0B-5D79-41C6-AF1A-9A7F750A1779 Feb 17 20 358 20 Rambling Rose Ave. # 103 West Covina 9 91791 D30222DF-3D9F-42F6-87B5-68D75E15A305 Feb 25 20 

Solution No. 2:

Query:

 DECLARE @ObjectID INT = OBJECT_ID('[Sales].[SalesTaxRate]') DECLARE @Text NVARCHAR(MAX) ;WITH cte AS ( SELECT txt = REPLACE(txt, '<t ' + c.name + '="', CHAR(13)), a = 2 FROM sys.columns c CROSS JOIN ( SELECT txt = ( SELECT [SalesTaxRateID] , [StateProvinceID] , [TaxType] , [TaxRate] , [rowguid] , [ModifiedDate] FROM [Sales].[SalesTaxRate] t FOR XML AUTO ) ) t WHERE c.[object_id] = @ObjectID AND c.column_id = 1 UNION ALL SELECT REPLACE(txt, '" ' + c.name + '="', CHAR(9)), a + 1 FROM cte JOIN sys.columns c ON c.[object_id] = @ObjectID AND c.column_id = a ) SELECT TOP 1 @Text = STUFF(REPLACE(txt, '"/>', ''), 1, 1, '') FROM cte ORDER BY a DESC OPTION (MAXRECURSION 100) PRINT @Text 

Output:

 1 1 1 14.0000 683DE5DD-521A-47D4-A573-06A3CDB1BC5D 2002-06-01T00:00:00 2 57 1 14.2500 05C4FFDB-4F84-4CDF-ABE5-FDF3216EA74E 2002-06-01T00:00:00 3 63 1 14.2500 D4EDB557-56D7-403C-B538-4DF5E7302588 2002-06-01T00:00:00 4 1 2 7.0000 F0D76907-B433-453F-B95E-16FCE73B807A 2002-06-01T00:00:00 10 41 3 7.0000 383D465B-E1D1-492A-83F3-AB3E9CBF3282 2002-06-01T00:00:00 23 36 1 6.7500 1753D75F-8357-4497-BC92-543F17BBF514 2002-06-01T00:00:00 31 14 3 17.5000 ABD185A9-5367-44E1-8A6E-71D083943F3C 2002-06-01T00:00:00 

Answer 2

Use STUFF (). try it

  SELECT STUFF((SELECT ' ' + (cast(AddressID as varchar(10))+','+ cast(AddressLine1 as varchar(500))+','+ cast(AddressLine1 as varchar(500))) FROM Person.Address FOR XML PATH('')), 1, 1, '')