I am looking for help to split a column based on criteria in other column and date range = 2022.01.12
This is my_table example :
| date | area | move | qty | item |
|---|---|---|---|---|
| 01.12 7:00 | L1a | in | 1 | item1 |
| 01.12 7:00 | L2 | out | 2 | item1 |
| 01.12 7:01 | L1b | in | 1 | item2 |
| 01.12 7:01 | L02 | out | 2 | item2 |
| 01.12 7:02 | L1a | in | 5 | item1 |
| 01.12 7:02 | L02 | out | 7 | item1 |
| 01.12 7:03 | L1 | in | 1 | item3 |
| 01.12 7:03 | L2 | out | 1 | item3 |
Expected result :
| date | area_in | move_in | qty | area_out | move_out | qty | item |
|---|---|---|---|---|---|---|---|
| 01.12 | L1a | in | 1 | L2 | out | 2 | item1 |
| 01.12 | L1b | in | 1 | L02 | out | 2 | item2 |
| 01.12 | L1a | in | 5 | L02 | out | 7 | item1 |
| 01.12 | L1 | in | 1 | L2 | out | 1 | item3 |
Looking forwards for your kind help .
Thanks, David
CodePudding user response：
I is a quite simple pivot for that data
But if the time don't fit together, you still would need a better group value
SELECT
[date],
MAX(CASE WHEN [move] = 'in' THen [area] ELSe NULL END) area_in,
MAX(CASE WHEN [move] = 'in' THen [move] ELSe NULL END) move_in,
MAX(CASE WHEN [move] = 'in' THen [qty] ELSe NULL END) qty_in,
MAX(CASE WHEN [move] = 'out' THen [area] ELSe NULL END) area_out,
MAX(CASE WHEN [move] = 'out' THen [move] ELSe NULL END) move_out,
MAX(CASE WHEN [move] = 'out' THen [qty] ELSe NULL END) qty_out
, [item]
FROM tab1
GROUP BY [date], [item]
ORDER By [date]
| date | area_in | move_in | qty_in | area_out | move_out | qty_out | item |
|---|---|---|---|---|---|---|---|
| 01.12 7:00 | L1a | in | 1 | L2 | out | 2 | item1 |
| 01.12 7:01 | L1b | in | 1 | L02 | out | 2 | item2 |
| 01.12 7:02 | L1a | in | 5 | L02 | out | 7 | item1 |
| 01.12 7:03 | L1 | in | 1 | L2 | out | 1 | item3 |
Warning: Null value is eliminated by an aggregate or other SET operation.