sql - convert any query result into a string -

using sql server 2005 , tsql, i'm looking way convert result of query (eg. select * person.address) 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 cursor way go? i'd mechanism generic can convert result of query string.

try 1 -

solution #1 (works 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 + ' char(' + cast(                     case when c.max_length = -1 or type_name(c.system_type_id) = 'uniqueidentifier'                                150                                else (c.max_length / 2) + 5                     end varchar(10)) + '))'      sys.columns c      c.[object_id] = object_id(@tablename)           , c.user_type_id = c.system_type_id xml path('')), 1, 2, ' ') + '  ' + @tablename + ' xml path('''')), 1, 1, '''')'  declare @temp table (t varchar(max))  insert @temp exec sys.sp_executesql @sql  declare @sql2 varchar(max)  select @sql2 = t @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       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 (      select txt = replace(txt, '<t ' + c.name + '="', char(13)), = 2      sys.columns c      cross join (           select txt = (                select                        [salestaxrateid]                     , [stateprovinceid]                     , [taxtype]                     , [taxrate]                     , [rowguid]                     , [modifieddate]                [sales].[salestaxrate] t                xml auto           )              ) t      c.[object_id] = @objectid           , c.column_id = 1       union       select replace(txt, '" ' + c.name + '="', char(9)), + 1       cte      join sys.columns c on c.[object_id] = @objectid           , c.column_id = ) select top 1 @text = stuff(replace(txt, '"/>', ''), 1, 1, '') cte order 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