在SQL Server 2014中将(文本)行转换为列

Question

我试图捕获特定组的行，然后将它们转换为命名脚本标题的列。

这是一个数据示例：

FORMULAS  | INSTRUCTIONS
----------+----------------------------------------------------
AF100120  | **ACETALDEHYDE WARNING - USE APPROPRIATE CAUTION**
AF100120  | ADD REMAINING ITEMS   
AF100120  | DISSOLVE ITEMS 1-2 IN 3 
AF100120  | IF HEAT USED, COOL TO ROOM TEMPERATURE 
AF100120  | MIX UNTIL HOMOGENEOUS                                                                               
AF100120  | MIXING TIMES VARY WITH BATCH SIZE                                                                   
AF100997  | 1) DISSOLVE THYMOL CRYSTALS IN ETHYL ALCOHOL. MIX WELL.
AF100997  | 2) ADD REMAINING ITEMS AND MIX UNTIL UNIFORM.
AF100997  | 3) FILTER IF NOT CLEAR

此脚本所需的输出为：

FORMULAS | INSTRUCTION #1 |INSTRUCTION #2 | INSTRUCTION #3 |INSTRUCTION #4 |INSTRUCTION #5|INSTRUCTION #6
AF100120 |**ACETALDEHYDE..| ADD REMAINING.| DISSOLVE ITEM. |IF HEAT USED.. |MIX UNTIL H   |MIXING TIM...
AF100997 |1) DISSOLVE THYM| 2) ADD REMAINI| FILTER IF NOT  | NULL          |NULL          |NULL          

如果有人能帮我处理代码，我将不胜感激：

SELECT *
FROM
(SELECT 
A.FormulaId AS FORMULAS, 
CAST(B.Instruction AS NVARCHAR(100)) AS [INSTRUCTIONS]
FROM 
Formulas AS A
LEFT JOIN 
FormulaProcedures AS B ON A.FormulaId = B.FormulaId
WHERE 
B.ProcedureType = 3 
AND PhaseType = 2 
AND YEAR(A.VersionDate) >= '2018' 
AND A.PrimaryVersion = 1 
AND A.FormulaId IN('AF100120', 'AF100997')
GROUP BY  
A.FormulaId, CAST(B.Instruction AS NVARCHAR(100))
) AS SourceTable 
PIVOT
(MAX([INSTRUCTIONS]) 
FOR [INSTRUCTIONS] IN ([INSTRUCTION #1], [INSTRUCTION #2],
[INSTRUCTION #3], [INSTRUCTION #4],
[INSTRUCTION #5], [INSTRUCTION #6])) AS PivotTable;

谢谢！

Answer 1

您可以使用包含ROW_NUMBER()函数的Dynamic Pivot，该函数的值按formulas分组，按instructions列排序，以便在迭代该分析函数时对列名进行编号：

DECLARE @cols  AS NVARCHAR(MAX),  @query AS NVARCHAR(MAX)
SELECT @cols = 
STUFF((SELECT DISTINCT ',' + QUOTENAME(CONCAT('formulas',[rn])) AS formulas
FROM 
(
SELECT f.*, 
ROW_NUMBER() OVER 
(PARTITION BY [formulas] ORDER BY [instructions]) AS rn
FROM [Formulas_Tab] f
) ff
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') 
,1,1,'');
SET  @query = 
N'SELECT *  
FROM
(
SELECT f.*, 
CONCAT(''formulas'',
ROW_NUMBER() OVER 
(PARTITION BY [formulas] ORDER BY [instructions])) AS rn
FROM [Formulas_Tab] f
) ff
PIVOT 
(
MAX([instructions]) FOR [rn] IN (' + @cols + N')
) p '
EXEC sp_executesql @query;

演示

如果你安装的数据库版本是2017+，那么你应该使用STRING_AGG()函数来确定@cols变量为

SELECT @cols =
SELECT STRING_AGG(QUOTENAME(CONCAT('formulas', [ rn ])), ',') AS formulas
FROM 
(
SELECT DISTINCT ROW_NUMBER() OVER
( PARTITION BY [ formulas ] ORDER BY [ instructions ]) AS rn
FROM [ Formulas_Tab ] f
) ff