Dynamically add columns to query results

I am looking for a query to summarized data representing manufacturing job operations in one line. For example, a job can have multiple operations and not all jobs have the same operations. Then concatenate the Operation with Hors and estimated hours.

JobNum	Operation	Hours	Estimate	Completed
1	Fabrication	5	6	1
1	Paint	1	1	1
1	Packaging	0	1	0
2	Fabrication	6	6	0
2	Welding	2	4	0
2	Paint	0	2	0
2	Packaging	0	1	0
3	Fabrication	3	2	1
3	Packaging	0.25	0.5	1

What I am looking for is something like this

JobNum	Operation	Operation	Operation	Operation
1	Fabrication (5/6)	Paint (1/1)	Packaging (0/1)
2	Fabrication (6/6)	Welding (2/4)	Paint (0/2)	Packaging (0/1)
3	Fabrication (3/2)	Packaging (0.25/0.5)

I tried using a Pivot, but I need to define all operations as columns leaving multiple NULL columns in each row.

JobNum	Fabrication	Welding	Paint	Packaging
1	Fabrication (5/6)	NULL	Paint (1/1)	Packaging (0/1)
2	Fabrication (6/6)	Welding (2/4)	Paint (0/2)	Packaging (0/1)
3	Fabrication (3/2)	NULL	NULL	Packaging (0.25/0.5)

Solution

One potential solution, assuming there is a max of 4 types of operations:

;WITH src AS 
(
  SELECT JobNum,
    rn = ROW_NUMBER() OVER (PARTITION BY JobNum ORDER BY @@SPID),
    [H/E] = CONCAT(Operation,' (',Hours,'/',Estimate,')')
  FROM dbo.whoknows AS w
)
SELECT JobNum, Operation = COALESCE([1], ''), 
               Operation = COALESCE([2], ''), 
               Operation = COALESCE([3], ''),
               Operation = COALESCE([4], '')
FROM src PIVOT 
(MAX([H/E]) FOR rn IN ([1],[2],[3],[4])) AS p;

Working db<>fiddle example.

If you need it to be smart about adapting to any number of operations, you can build a dynamic PIVOT:

DECLARE @numOps int, 
  @output nvarchar(max), 
  @cols   nvarchar(max),
  @sql    nvarchar(max);

SELECT @numOps = COUNT(DISTINCT Operation) FROM dbo.whoknows;

;WITH OpCount AS
(
  SELECT rn = QUOTENAME(ROW_NUMBER() OVER (ORDER BY @@SPID))
  FROM STRING_SPLIT(REPLICATE(',', @numOps - 1), ',')
)
SELECT @output = STRING_AGG(CONCAT('Operation = COALESCE(', 
   rn, ', '''')'),',
'), @cols = STRING_AGG(rn, ',') FROM OpCount;

SET @sql = CONCAT(N';WITH src AS 
(
  SELECT JobNum,
    rn = ROW_NUMBER() OVER (PARTITION BY JobNum ORDER BY @@SPID),
    [H/E] = CONCAT(Operation,'' ('',Hours,''/'',Estimate,'')'')
  FROM dbo.whoknows AS w
)
SELECT JobNum, ', @output, ' FROM src PIVOT 
(MAX([H/E]) FOR rn IN (', @cols, ')) AS p;');

EXEC sys.sp_executesql @sql;

Also with a db<>fiddle example.

Output in both cases:

JobNum	Operation	Operation	Operation	Operation
1	Fabrication (5/6)	Paint (1/1)	Packaging (0/1)
2	Fabrication (6/6)	Welding (2/4)	Paint (0/2)	Packaging (0/1)
3	Fabrication (3/2)	Packaging (0.25/0.5)

And finally, for the late-breaking requirement of SQL Server 2014 (<shudder>):

DECLARE @numOps int,           @output nvarchar(max), 
        @sql    nvarchar(max), @cols   nvarchar(max);

SELECT @numOps = COUNT(DISTINCT Operation) FROM dbo.whoknows;

;WITH OpCount AS (
  SELECT r = 1 UNION ALL
  SELECT r+1 FROM OpCount WHERE r < @numOps
), ocols AS 
(
    SELECT c = STUFF((SELECT ',' 
      + CONCAT('Operation = COALESCE(', QUOTENAME(r), ', '''')')
    FROM OpCount ORDER BY r
    FOR XML PATH(''), TYPE).value(N'text()[1]', N'nvarchar(max)'),1,1,'')
), pcols AS 
(
    SELECT c = STUFF((SELECT CONCAT(',',QUOTENAME(r))
    FROM OpCount ORDER BY r
    FOR XML PATH(''), TYPE).value(N'text()[1]', N'nvarchar(max)'),1,1,'')
)
SELECT @output = (SELECT c FROM ocols),
       @cols   = (SELECT c FROM pcols)
  OPTION (MAXRECURSION 255);

SET @sql = CONCAT(N';WITH src AS (
  SELECT JobNum,
    rn = ROW_NUMBER() OVER (PARTITION BY JobNum ORDER BY @@SPID),
    [H/E] = CONCAT(Operation,'' ('',Hours,''/'',Estimate,'')'')
  FROM dbo.whoknows AS w )
SELECT JobNum, ', @output, ' FROM src PIVOT 
(MAX([H/E]) FOR rn IN (', @cols, ')) AS p;');

EXEC sys.sp_executesql @sql;

Working example here. That could probably be tidier but old, unsupported versions aren't worth the effort IMHO.