So here is an example table of my SCD type 2 output -
| project | name | account | contract value | start date | end date |
|---|---|---|---|---|---|
| 1 | Roy | Account 1 | 500 | 1/1/2023 | 5/1/2023 |
| 1 | Roy | Account 2 | 550 | 5/1/2023 | Today's date |
| 2 | Roy | Account 3 | 600 | 1/1/2023 | 6/1/2023 |
| 2 | Roy2 | Account 3 | 650 | 6/1/2023 | 10/1/2023 |
| 2 | Roy | Account 2 | 650 | 10/1/2023 | Today's date |
The start date and end date indicate for how long an the values of the project remain the same. For example, for project 1 the values of name, account and contract value remained the same from 1st of Jan to 5th of Jan. On the 5th of Jan we can see that the value of account and contract value changed and since then there has been no change, hence today's date for end date.
In this table every measure I want to capture is a column (pivoted up), but I want to change this data to a pivoted down structure as shown below -
| project | pivot field name | pivot field value | start date | end date |
|---|---|---|---|---|
| 1 | name | Roy | 01-01-2023 | Today's date |
| 1 | account | Account 1 | 01-01-2023 | 05-01-2023 |
| 1 | account | Account 1 | 05-01-2023 | Today's date |
| 1 | contract value | 500 | 01-01-2023 | 05-01-2023 |
| 1 | contract value | 550 | 05-01-2023 | Today's date |
| 2 | name | Roy | 01-01-2023 | 06-01-2023 |
| 2 | name | Roy2 | 06-01-2023 | 10-01-2023 |
| 2 | name | Roy | 10-01-2023 | Today's date |
| 2 | account | Account 3 | 01-01-2023 | 10-01-2023 |
| 2 | account | Account 2 | 10-01-2023 | Today's date |
| 2 | contract value | 600 | 01-01-2023 | 06-01-2023 |
| 2 | contract value | 650 | 06-01-2023 | Today's date |
whats the best way to do this? Happy to get a solution in SQL or Python!