Dynamically add columns to query results

z31licg0  于 2023-02-28  发布在  其他
关注(0)|答案(2)|浏览(117)

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

JobNumOperationOperationOperationOperation
1Fabrication (5/6)Paint (1/1)Packaging (0/1)
2Fabrication (6/6)Welding (2/4)Paint (0/2)Packaging (0/1)
3Fabrication (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.

JobNumFabricationWeldingPaintPackaging
1Fabrication (5/6)NULLPaint (1/1)Packaging (0/1)
2Fabrication (6/6)Welding (2/4)Paint (0/2)Packaging (0/1)
3Fabrication (3/2)NULLNULLPackaging (0.25/0.5)
js5cn81o

js5cn81o1#

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) | | |

6qftjkof

6qftjkof2#

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

相关问题