Dynamically add columns to query results

Question

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)

Thats pretty much how SQL works... its a data storage and retrieval system, not a display system. Your front end will be able to do that much easier. — Dale K
– Dale K, Commented Feb 22, 2023 at 21:49
This would require dynamic SQL, and is more than trivial. Your presentation layer, as mentioned, would be a much better choice of tool to create this result set. — Thom A
– Thom A ♦, Commented Feb 22, 2023 at 21:52
Do you need it as separate columns or would a single column like "Fabrication (5/6), Paint (1/1), Packaging (0/1)" be sufficient? If the latter, maybe you just want to use string_agg around a subquery. — EdmCoff
– EdmCoff, Commented Feb 22, 2023 at 21:55
Is there a known set of operations or will it dynamically have to adapt to new operation types (even typos - I feel like there should be an Operations table and this should just have foreign keys)? Is there a deterministic way to dictate that Welding comes before Paint and Paint comes before Packaging? Is it enforced that each JobNum can only have one row per operation? — Aaron Bertrand
– Aaron Bertrand, Commented Feb 22, 2023 at 22:06
You can use the ROW_NUMBER() OVER(...) function to assign sequnce numbers to the operations within each job and pivot on that. An alternative to PIVOT that is sometimes easier to manage is conditional aggregation of the form SELECT ..., MAX(CASE WHEN RowNum = 3 THEN Operation END) AS Oparation3. (E.g., what @siggemannen just posted.) — T N
– T N, Commented Feb 22, 2023 at 22:11

Aaron Bertrand · Accepted Answer · 2023-03-03 17:11:36Z

1

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.

edited Mar 3, 2023 at 17:11

answered Feb 22, 2023 at 22:19

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

siggemannen Over a year ago

ROW_NUMBER() OVER (ORDER BY @@SPID) i didn't know one could use niladic functions here! Even USER works. And i've been wasting time doing (select NULL) all those years

Aaron Bertrand Over a year ago

@siggemannen I use @@SPID unless I want to prevent parallelism in which case I use @@TRANCOUNT. Picked that up from Paul White and/or Itzik Ben-Gan several years back.

siggemannen Over a year ago

That's very interesting, thanks for sharing this information, i'll have to read up on it. Btw, i guess this is a bit off topic, but why is there difference between row_number() over(order by (select newid())) and row_number() over(order by newid())? First doesn't randomize while second does. There's no difference when you select the values outside of the row_number

Aaron Bertrand Over a year ago

(select newid()) -> converted to constant first (at least in that construct, but not as part of the SELECT list). order by newid() is evaluated for every row no matter where it is mentioned.

mchernecki Over a year ago

I like the second example, it does what I need. However, I did not realize that this one would running on our 2014 server and STRING_AGG is not available.

|

siggemannen · Accepted Answer · 2023-02-22 22:39:14Z

1

Just a little tip, don't "start" any SQL solution by using pivots, it should only be for style points when you're already done. There's nothing magical about that operator, it very seldom saves any kind of time, and it has a ton of limitations and awkward syntax.

If you have a fixed amount operation you can use the following pseudo-pivot instead:

select  jobnum
,   ISNULL(MAX(case when sort = 1 then Operation end), '') operation
,   ISNULL(MAX(case when sort = 2 then Operation end), '') operation
,   ISNULL(MAX(case when sort = 3 then Operation end), '') operation
,   ISNULL(MAX(case when sort = 4 then Operation end), '') operation
from (
    select jobnum, row_number() over(partition by jobNum order by case operation when 'Fabrication' then 0 when 'Welding' then 1 when 'paint' then 2 when 'packaging' then 3 else 4 end)AS sort
    ,   Operation + ' (' + CAST(hours as varchar(10)) + '/' + CAST(estimate as varchar(10)) + ')' AS Operation
    from (
        VALUES  (1, N'Fabrication', 5, 6, 1)
        ,   (1, N'Paint', 1, 1, 1)
        ,   (1, N'Packaging', 0, 1, 0)
        ,   (2, N'Fabrication', 6, 6, 0)
        ,   (2, N'Welding', 2, 4, 0)
        ,   (2, N'Paint', 0, 2, 0)
        ,   (2, N'Packaging', 0, 1, 0)
        ,   (3, N'Fabrication', 3, 2, 1)
        ,   (3, N'Packaging', 0.25, 0.5, 1)
    ) t (JobNum,Operation,Hours,Estimate,Completed)
    )x
group by JobNum

The key here is to realize the sort which puts the columns into correct slot of your table, this way every operation can be easily flipped by using MAX(CASE WHEN...) construct which is pretty much what pivot uses.

This will need some work if you have dynamic and unlimited number of operations, I'll leave it as exercise for the reader

edited Feb 22, 2023 at 22:39

answered Feb 22, 2023 at 22:09

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

2 Comments

T N Over a year ago

Nice answer. I'll offer one suggestion: A

cross apply ( select Operation + ' (' + CAST(hours as varchar(10)) + '/' + CAST(estimate as varchar(10)) end + ')') AS OperationSummary ) S

can be used to eliminate the need for duplicate expressions.

siggemannen Over a year ago

Good call @TN , changed it a bit

Collectives™ on Stack Overflow

Dynamically add columns to query results

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related