插入多个逗号分隔的列作为一个单列在SQL服务器

Question

我有一个表像这样

CREATE TABLE #tbl(PackId NVARCHAR(MAX),AmntRemain NVARCHAR(MAX),AmntUsed NVARCHAR(MAX),IsCount NVARCHAR(MAX),IsValue NVARCHAR(MAX)) 

INSERT INTO #tbl VALUES('1,2','10,20','10,20','1,0','0,1') 

上面的表格输出

我关心的是如何获取输出像下面

如何将数据插入上表数据表中作为独立行的所有列个别值

Answer 1

除非从另一个表中插入数据，否则您将不得不为每一行创建单独的插入语句创造。

Answer 2

你想插入2行。尝试：

INSERT INTO #tbl VALUES 
('1','10','10','1','0') 
,('2','20','20','0','1') 

Answer 3

几乎任何分析/拆分功能都可以。下面提供的还会返回一个项目序列号，它可用于连接并将适当行中的各个元素连接起来。

元素是不固定的数量，一个记录可以有2个，而另一个具有5

我要补充，如果不能或不想用一个UDF，这将是一件小事创建在线方法。

例

Declare @Staging TABLE (PackId NVARCHAR(MAX),AmntRemain NVARCHAR(MAX),AmntUsed NVARCHAR(MAX),IsCount NVARCHAR(MAX),IsValue NVARCHAR(MAX)) 
INSERT INTO @Staging VALUES 
('1,2','10,20','10,20','1,0','0,1') 

Select B.* 
From @Staging A 
Cross Apply (
       Select PackId  = B1.RetVal 
         ,AmntRemain = B2.RetVal 
         ,AmntUsed = B3.RetVal 
         ,IsCount = B4.RetVal 
         ,IsValue = B5.RetVal 
       From [dbo].[udf-Str-Parse-8K](A.PackId,',')  B1 
       Join [dbo].[udf-Str-Parse-8K](A.AmntRemain,',') B2 on B1.RetSeq=B2.RetSeq 
       Join [dbo].[udf-Str-Parse-8K](A.AmntUsed,',') B3 on B1.RetSeq=B3.RetSeq 
       Join [dbo].[udf-Str-Parse-8K](A.IsCount,',') B4 on B1.RetSeq=B4.RetSeq 
       Join [dbo].[udf-Str-Parse-8K](A.IsValue,',') B5 on B1.RetSeq=B5.RetSeq 
      ) B 

返回

PackId AmntRemain AmntUsed IsCount IsValue 
1  10   10   1  0 
2  20   20   0  1 

的UDF如果感兴趣

CREATE FUNCTION [dbo].[udf-Str-Parse-8K] (@String varchar(max),@Delimiter varchar(25)) 
Returns Table 
As 
Return ( 
    with cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)), 
      cte2(N) As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A), 
      cte3(N) As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter), 
      cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S) 

    Select RetSeq = Row_Number() over (Order By A.N) 
      ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L))) 
    From cte4 A 
); 
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/ 
--Much faster than str-Parse, but limited to 8K 
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',') 
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||') 

Answer 4

你不应该像这样存储你的数据。你应该真正修复你的问题的所有评论的etl进程和数据库模式。

使用cross apply(values ...)到UNPIVOT数据，分割字符串和使用条件汇聚到数据枢转回行：

在SQL Server 2016+可以使用string_split()。

在SQL Server预-2016，采用CSV分离器表值函数由杰夫MODEN：

;with cte as (
    select 
     Id = row_number() over (order by (select null)) /* adding an id to uniquely identify rows */ 
    , * 
    from #tbl 
) 
select 
    cte.Id 
    , s.ItemNumber 
    , PackId  = max(case when u.column_name = 'PackId' then s.item end) 
    , AmntRemain = max(case when u.column_name = 'AmntRemain' then s.item end) 
    , AmntUsed = max(case when u.column_name = 'AmntUsed' then s.item end) 
    , IsCount = max(case when u.column_name = 'IsCount' then s.item end) 
    , IsValue = max(case when u.column_name = 'IsValue' then s.item end) 
from cte 
    cross apply (values ('PackId',PackId),('AmntRemain',AmntRemain),('AmntUsed',AmntUsed),('IsCount',IsCount),('IsValue',IsValue)) u (column_name,column_value) 
    cross apply dbo.delimitedsplit8K(u.column_value,',') s 
group by cte.Id, s.ItemNumber 

rextester演示：http://rextester.com/ZIFFQX41171

回报：

+----+------------+--------+------------+----------+---------+---------+ 
| Id | ItemNumber | PackId | AmntRemain | AmntUsed | IsCount | IsValue | 
+----+------------+--------+------------+----------+---------+---------+ 
| 1 |   1 |  1 |   10 |  10 |  1 |  0 | 
| 1 |   2 |  2 |   20 |  20 |  0 |  1 | 
+----+------------+--------+------------+----------+---------+---------+ 

分割字符串参考：

• Tally OH! An Improved SQL 8K “CSV Splitter” Function - Jeff Moden
• Splitting Strings : A Follow-Up - Aaron Bertrand
• Split strings the right way – or the next best way - Aaron Bertrand
• string_split() in SQL Server 2016 : Follow-Up #1 - Aaron Bertrand
• Ordinal workaround for **string_split()** - Solomon Rutzky