| ID | Title | Created by | Position | Date From | Date To |
|---|
| 1 | abc | a1 | started | 01/01/19 | 01/01/19 |
| 1 | abc | a2 | under review | 01/31/19 | 02/12/19 |
| 2 | xyz | b1 | started | 02/22/19 | 02/22/19 |
| 2 | xyz | b1 | under review | 02/23/19 | |
| 2 | xyz | b1 | in progress | 03/01/19 | 03/10/2019 |
I am attempting to create a custom report in which the position values are columns. Example for Table A is above and the end result i am trying to achieve is below:
| ID | started_from | started_to | under review_from | under review_to | In progress_from | In progress_to |
|---|
| 1 | 01/01/19 | 01/01/19 | 01/31/19 | 02/12/19 | | |
| 2 | 02/22/19 | 02/22/19 | 02/23/19 | | 03/01/19 | 03/10/2019 |
I was able to use pivot (below) and is able create columns for started, under review and in progress based on the values for from. However it is outputting all the columns in the table
SELECT *
FROM Table A
PIVOT (
MAX(Date From) FOR Position IN ('started','under review','in progress')
);
I tried to mention the values required (below), but i get an error invalid character
SELECT
ID,
Position,
Date From,
Date to
FROM Table A
PIVOT (
MAX(Date From) FOR Position IN ('started','under review','in progress')
);
Please let me know if i am on right track.