如何根据子字符串在字符串中的位置及其长度生成PIVOT输出

Question

我需要能够PIVOT/映射文本字符串(表1)。

填充

CardType	CardCode	TextString	ColumnName	MaxLengthOfString
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	类型	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Class	4
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	PackSize	8
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	InvPack	3
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	CalcAmount	8
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	CostCode	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	UomCode	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Uom	5
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Fill1	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	FoodStamp	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Fill2	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	税收	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Fill3	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	盘	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Fill4	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	NoStock	2
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Fill5	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	CalcPack2	7
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	InvFlag	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Fill6	1
A	2	I101G2.2 OZ 001 0002200 L Y 0010000	Fill7	99

下面是一个SQL脚本，它将构造和执行动态SQL来提取字段。它定义了几个SQL模板片段，然后逐步构建要使用REPLACE()和STRING_AGG()函数组合执行的最终SQL

-- Define SQL templates to be used later
DECLARE @SelectItemTemplate VARCHAR(MAX) =
'    SUBSTRING(PaddedTextString, <Start>, <Length>) AS <ColumnName>'
DECLARE @SelectItemSeparator VARCHAR(20) = ',
'
DECLARE @SqlTemplate VARCHAR(MAX) = '
SELECT T.CardType, T.CardCode,
<Selectlist>
FROM Table1 T
CROSS APPLY (
SELECT ISNULL(TextString, '''') + SPACE(<PadLength>) AS PaddedTextString
) PTS
'
-- Construct select list to map each substring to a column.
-- Exckude "fill" columns.
DECLARE @SelectList VARCHAR(MAX) = (
SELECT STRING_AGG(SI.SelectItem, @SelectItemSeparator)
WITHIN GROUP(ORDER BY C.Seq)
FROM (
SELECT *,
SUM(C.MaxLengthOfString) OVER(ORDER BY C.Seq) - MaxLengthOfString + 1 AS StartPos
FROM ColumnConfig C
) C
CROSS APPLY (
SELECT REPLACE(REPLACE(REPLACE(
@SelectItemTemplate
, '<Start>', CAST(C.StartPos AS VARCHAR))
, '<Length>', CAST(C.MaxLengthOfString AS VARCHAR))
, '<ColumnName>', QUOTENAME(C.ColumnName))
AS SelectItem
) SI
WHERE C.ColumnName NOT LIKE 'Fill%'
)
-- Calculate length needed for padding to ensure all SUBSTRINGs are covered.
DECLARE @PadLength INT = (SELECT SUM(MaxLengthOfString) FROM ColumnConfig)
-- Build final SQL
DECLARE @Sql NVARCHAR(MAX) = REPLACE(REPLACE(
@SqlTemplate
, '<SelectList>', @SelectList)
, '<PadLength>', @PadLength)

-- Print and execute
PRINT @Sql
EXEC (@Sql)

生成动态SQL:

SELECT T.CardType, T.CardCode,
SUBSTRING(PaddedTextString, 1, 1) AS [Type],
SUBSTRING(PaddedTextString, 2, 4) AS [Class],
SUBSTRING(PaddedTextString, 6, 8) AS [PackSize],
SUBSTRING(PaddedTextString, 14, 3) AS [InvPack],
SUBSTRING(PaddedTextString, 18, 8) AS [CalcAmount],
SUBSTRING(PaddedTextString, 26, 1) AS [CostCode],
SUBSTRING(PaddedTextString, 27, 1) AS [UomCode],
SUBSTRING(PaddedTextString, 28, 5) AS [Uom],
SUBSTRING(PaddedTextString, 34, 1) AS [FoodStamp],
SUBSTRING(PaddedTextString, 36, 1) AS [Tax],
SUBSTRING(PaddedTextString, 38, 1) AS [Disc],
SUBSTRING(PaddedTextString, 40, 2) AS [NoStock],
SUBSTRING(PaddedTextString, 43, 7) AS [CalcPack2],
SUBSTRING(PaddedTextString, 50, 1) AS [InvFlag]
FROM Table1 T
CROSS APPLY (
SELECT ISNULL(TextString, '') + SPACE(150) AS PaddedTextString
) PTS

结果:

<表类>CardTypeCardCode型类PackSizeInvPackCalcAmountCostCodeUomCodeUom食品券税收盘NoStockCalcPack2InvFlagtbody><<tr>2我101 g td> 2.2盎司0010002200LY0010000