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!
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
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;
FOR XML AUTO or FOR XML RAW is closer to what you want.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
SELECT CONCAT(column1,',',column2) FROM table- why do you need*?