0

I have a query that returns a row 

SELECT *
FROM table
WHERE id = 1;

I want to save the result into a nvarchar sql variable. I have seen similar questions Convert SQL Server result set into string but they only use select with the name of the columns, never with *.

select *
from table
where id = 1
for xml path ('')

However the answer is <column1>value1</column1> <column2>value2</column2> and I just want it to be value1, value2

Is there a way to achieve this? thank you!

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
Mariana
  • 71
  • 1
  • 7

3 Answers3

1

If open to a helper function.

This will convert virtually any row, table or query to a string (delimited or not).

In the following examples I selected a PIPE delimiter with a CRLF line terminator.

Please note the usage and placement of _RN when a line terminator is required. Also note the ,ELEMENTS XSINIL ... this will included null values as empty string. If you want to exclude null values, simply omit the ,ELEMENTS XSINIL

Example as Entire Table or dbFiddle

Declare @YourTable Table (id int,[col_1] varchar(50),[col_2] varchar(50),[col_3] varchar(50),[col_n] varchar(50))  Insert Into @YourTable Values 
 (1,'data1','data2','data3','data4')
,(2,'data5','data6','data7','data8')

-- Entire Table 
Declare @XML xml = (Select *,_RN=Row_Number() over (Order By (Select null)) From @YourTable for XML RAW,ELEMENTS XSINIL )
Select [dbo].[svf-str-Data-To-Delimited]('|',char(13)+char(10),@XML)

Returns 

1|data1|data2|data3|data4
2|data5|data6|data7|data8

Example as Row Based

Select A.ID
      ,AsAString = [dbo].[svf-str-Data-To-Delimited]('|',char(13)+char(10),B.XMLData)
 From  @YourTable A
Cross Apply ( values ( (select a.* for xml RAW,ELEMENTS XSINIL )) )B(XMLData)

Returns

ID  AsAString
1   1|data1|data2|data3|data4
2   2|data5|data6|data7|data8

The Function if Interested

CREATE Function [dbo].[svf-str-Data-To-Delimited] (@Delim varchar(50),@EOL varchar(50),@XML xml)
Returns varchar(max)
Begin

Return(
        Select convert(nvarchar(max),(
            Select case when Item='_RN' then ''
                        else case when nullif(lead(Item,1) over (Order by Seq),'_RN') is not null  
                                  then concat(Value,@Delim)
                                  else concat(Value,@EOL)
                        end 
                    end
             From (
                    Select Seq   = row_number() over(order by (select null))
                          ,Item  = xAttr.value('local-name(.)', 'nvarchar(100)')
                          ,Value = xAttr.value('.','nvarchar(max)')
                     From  @XML.nodes('/row/*') xNode(xAttr)
                   ) A
             Order By Seq
             For XML Path (''),TYPE).value('.', 'nvarchar(max)') )
)

End
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

You can easily store the result as an XML string:

select *
from (values (1, 'x', getdate())) v(id, a, b)
where id = 1
for xml path ('');

Or as a JSON string:

select *
from (values (1, 'x', getdate())) v(id, a, b)
where id = 1
for json auto;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

If you don't mind Using dynamic SQL (and INFORMATION_SCHEMA dictionary), for example, for SQL Server this works:

DECLARE @sql nvarchar(max) = '', @result nvarchar(max), @id int = 1 SELECT @sql += '+'',''+convert(nvarchar,' + QUOTENAME(column_name) +')' from INFORMATION_SCHEMA.columns where table_name = 'Student' SET @sql = 'select @result=' + stuff(@sql,1,5,'') + ' from student where id = ' + CAST(@id as nvarchar) EXECUTE sp_executesql @sql, N'@result nvarchar(max) OUTPUT', @result=@result OUTPUT SELECT @result as MyOutput

mpogrebnoy
  • 16
  • 3