convert any query result into a string

Question

Using Sql Server 2005 and tsql, I'm looking for a way to convert the result of any query (eg. select * from Person.Address) into a big 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        

Would a cursor be the way to go? I'd like the mechanism to be generic so that it can convert the result of any query into a string.

Answer 1

Try this one -

Solution #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 #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