CODEWITHSUNDEEP
sql-serverstring
ang
ang

Reputation: 1581

How to select SQL Server table as tab delimited string

I'd like to to select a table from SQL and turn it into preferably tab delimited, otherwise CSV format.

| col_1 | col_2 | col_3 | col_n | 
+-------+-------+-------+-------+
| data1 | data2 | data3 | data4 | 
| data5 | data6 | data7 | data8 |

Using for xml path I can get select the table as string with <tag-name> delimiters.

let str = stuff((select * from db_name for xml path('')), 1, 1 '')

Actual output

<col_1>data1</col_1><col_2>data2</col_2><col_3>data3</col_3><col_4>data4</col_4>
<col_5>data5</col_5><col_6>data6</col_6><col_7>data7</col_7><col_8>data8</col_8>

Expected string output

data1   data2    data3    data4 
data5   data6    data7    data8

The table is very wide, so using a method like concat and listing each column isn't very maintainable.

How could I select the above table as a tab delimited string?

Upvotes: 1

Views: 2852

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81970

Notice the _RN is required in the supplying query. Could also be a TVF

Also, the ,ELEMENTS XSINIL is required to keep the "position" of null values, but they will NOT be null, bur rather an empty string

Example dbFiddle

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

Select [dbo].[svf-str-Data-Tab-delimited]((Select *,_RN=Row_Number() over (Order By (Select null)) From @YourTable for XML RAW,ELEMENTS XSINIL ))

Returns

data1    data2   data3   data4   
data5    data6   data7   data8

The Function if Interested

CREATE Function [dbo].[svf-str-Data-Tab-delimited] (@XML xml)
Returns varchar(max)
Begin
    Declare @S varchar(max) = ''
    Select @S = @S+ case when ColItem<>'_RN' then ColValue else '' end + case when ColItem='_RN' then char(13)+char(10) else char(9) end
     From (
            Select ColSeq   = row_number() over(order by (select null))
                  ,ColItem  = xAttr.value('local-name(.)', 'nvarchar(100)')
                  ,ColValue = xAttr.value('.','nvarchar(max)')
             From  @XML.nodes('/row/*') xNode(xAttr)
          ) A
     Order By ColSeq
    Return @S
End

EDIT -

The original solution had a trailing tab. I also modified the function by adding parameters for the delimiter and EOL.

Example Updated dbFiddle

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

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(9),char(13)+char(10),@XML)

Returns

data1   data2   data3   data4
data5   data6   data7   data8

The Update Function

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

Return(
        Select convert(varchar(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

Upvotes: 2

Related Questions