By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Formulas_Tab(
[formulas] VARCHAR(75),
[instructions] VARCHAR(75)
);
INSERT INTO Formulas_Tab
VALUES('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')
9 rows affected
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;
| formulas | formulas1 | formulas2 | formulas3 | formulas4 | formulas5 | formulas6 |
|---|---|---|---|---|---|---|
| AF100120 | ACETALDEHYDE WARNING - USE APPROPRIATE CAUTION | ADD REMAINING ITEMS | DISSOLVE ITEMS 1-2 IN 3 | IF HEAT USED, COOL TO ROOM TEMPERATURE | MIX UNTIL HOMOGENEOUS | MIXING TIMES VARY WITH BATCH SIZE |
| AF100997 | 1) DISSOLVE THYMOL CRYSTALS IN ETHYL ALCOHOL. MIX WELL. | 2) ADD REMAINING ITEMS AND MIX UNTIL UNIFORM. | 3) FILTER IF NOT CLEAR | null | null | null |