Forum Discussion
Making a single row with multiple new columns
Hi, I have an SQL extract that produces a table forecasting the dates for mainteance events but it produces a row for each event, with a single week-year that the activity would take place. I would like to group all the other data and and add extra columns for the recuring events. Is this possible ( I have approximately 7000 rows, and dates going all the way out to the year 2044) ? Thanks in advance.
A sample table and desired results look like:
Sample
| SiteID | Loc | Freq | PM Num | JP Num | Type | Week |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-01 |
| HAM | HAM1 | 3 M | Z1049 | JP8508 | PM | 2024-01 |
| WLG | WLG/02 | 12 M | G1190 | JP2353 | CM | 2024-01 |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-05 |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-09 |
| HAM | HAM1 | 3 M | Z1049 | JP8508 | PM | 2024-13 |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-13 |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-17 |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-21 |
| HAM | HAM1 | 3 M | Z1049 | JP8508 | PM | 2024-25 |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-25 |
Desired result
| SiteID | Loc | Freq | PM Num | JP Num | Type | Week | Week (1) | Week (2) | Week (3) | Week (4) | Week (5) | Week (6) |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 2024-01 | 2024-05 | 2024-09 | 2024-13 | 2024-17 | 2024-21 | 2024-25 |
| HAM | HAM1 | 3 M | Z1049 | JP8508 | PM | 2024-01 | 2024-13 | null | null | null | null | null |
| WLG | WLG/02 | 12 M | G1190 | JP2353 | CM | 2024-01 | null | null | null | null | null | null |
Or the result could be as follows, I don't mind 🙂
| SiteID | Loc | Freq | PM Num | JP Num | Worktype | 2024-01 | 2024-02 | 2024-03 | 2024-04 | 2024-05 | 2024-06 | 2024-07 | 2024-08 | 2024-09 | 2024-10 | 2024-11 | 2024-12 | 2024-13 | 2024-14 | 2024-15 | 2024-16 | 2024-17 | 2024-18 | 2024-19 | 2024-20 | 2024-21 | 2024-22 | 2024-23 | 2024-24 | 2024-25 |
| TPO | TPO1 | 4 W | T1436 | JP4739 | PM | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| HAM | HAM1 | 3 M | Z1049 | JP8508 | PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| WLG | WLG/02 | 12 M | G1190 | JP2353 | CM | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Hi Anonymous ,
Yo can Pivot the Week by Type.
Buuuuut. It is advisable to have more columns than to have more columns
Why do you need to do this kind of step in M query?
If you just want to show the data by week, you can use the matrix visual instead of doing it here
1 Reply
Hi Anonymous ,
Yo can Pivot the Week by Type.
Buuuuut. It is advisable to have more columns than to have more columns
Why do you need to do this kind of step in M query?
If you just want to show the data by week, you can use the matrix visual instead of doing it here