sql-server - 如何选择 SQL Server 表作为制表符分隔的字符串

Question

我想从 SQL 中选择一个表并将其转换为最好是制表符分隔的，否则为 CSV 格式。

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

使用for xml path我可以选择表作为带有<tag-name>分隔符的字符串。

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

实际输出

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

预期的字符串输出

data1   data2    data3    data4 
data5   data6    data7    data8 

该表非常宽，因此使用类似concat并列出每一列的方法不是很容易维护。

如何选择上表作为制表符分隔的字符串？

标签： sql-serverstring

Answer 1

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