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) |
2条答案
按热度按时间js5cn81o1#
One potential solution, assuming there is a max of 4 types of operations:
Working db<>fiddle example .
If you need it to be smart about adapting to any number of operations, you can build a dynamic
PIVOT
: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) | | |
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:
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