tsql - 解析单列表中的列和数据

Question

我有一个表@tbl_rawData，我从一个非常动态的文件(没有列的固定位置)加载数据，所以我只在一列中加载整个文件。

我需要为每一列获取所需的列和相应的数据。

我对 @ 中维护的 ID、customername、prodname 和 regency 列感兴趣tbl_collist 表，但将来，也许我需要更多的列，然后我会在 @tbl_collist 中插入更多的列名。

declare @tbl_rawData table (fileData varchar(max))
insert into @tbl_rawData 
    (fileData)
values
    ('#Columns: ID|customername|addresss|agency|prodname|valuation|regency|ceptam|agan|yaha|citr'),
    ('11|jeev|a-161 kv|criu|uio|1800000|abap|yes|no|1000|200|'),
    ('12|poon|b-278 abv|criu|uio|50000|pyt|yes|no|2700|300|')

declare @tbl_collist table (colName varchar(100))
insert into @tbl_collist 
    (colName)
values
    ('ID'),
    ('customername'),
    ('prodname'),
    ('regency')

我想要如下所示的输出。

 id   customername   prodname    regency 
 11   jeev           uio         abap 
 12   poon           uio         pyt 

Answer 1

获取 dbo.DelimitedSplit8K 的副本然后:

-- Sample Data
declare @tbl_rawData table (fileData varchar(max));
insert into @tbl_rawData (fileData)
values
    ('#Columns: ID|customername|addresss|agency|prodname|valuation|regency|ceptam|agan|yaha|citr'),
    ('11|jeev|a-161 kv|criu|uio|1800000|abap|yes|no|1000|200|'),
    ('12|poon|b-278 abv|criu|uio|50000|pyt|yes|no|2700|300|');

declare @tbl_collist table (colName varchar(100))
insert into @tbl_collist (colName)
values('ID'),('customername'),('prodname'),('regency');

-- Solution
WITH Split AS
(
  SELECT      s.ItemNumber
  FROM        @tbl_rawData AS t
  CROSS APPLY dbo.DelimitedSplit8K(t.fileData,'|') AS s
  JOIN        @tbl_collist AS c ON s.Item = c.colName 
                                OR (ItemNumber = 1 AND c.colName = 'ID')
  WHERE       t.fileData LIKE '#%'
)
SELECT 
  ID           = MAX(IIF(s.ItemNumber = 1, s.Item, NULL)),
  customername = MAX(IIF(s.ItemNumber = 2, s.Item, NULL)),
  prodname     = MAX(IIF(s.ItemNumber = 5, s.Item, NULL)),
  regency      = MAX(IIF(s.ItemNumber = 7, s.Item, NULL))
FROM        @tbl_rawData AS t
CROSS APPLY dbo.DelimitedSplit8K(t.fileData,'|') AS s
WHERE       t.fileData NOT LIKE '#%'
  AND       s.ItemNumber IN (SELECT split.ItemNumber FROM split)
GROUP BY    t.fileData;

返回:

ID  customername  prodname  regency    
--- ------------- --------- -----------
11  jeev          uio       abap
12  poon          uio       pyt