Using UNPIVOT twice in the same query-CodePudding

Hi I've ran into the following problem:

I have a table with about 1600 rows that has the following structure:

StoreID | Target1 | Target2 | Target3 | Date1      | Date2      | Date3
101     | X       | Y       | Z       | 01-01-2021 | 02-01-2021 | 03-01-2021

I'd like to transform this table into

StoreID | Target | TargetDate
101     | X      | 01-01-2021
101     | Y      | 02-01-2021
101     | Z      | 03-01-2021

I'm quite new to SQL but my idea was to UNPIVOT(Target1, Target2, Target3) as one column and next to it UNPIVOT(Date1, Date2, Date3) as another column. I believe this should be possible as both columns would have the same length.

However numerous efforts have led to the same result where I end up with too many rows as each target ends up with 3 dates. Is there any way to distinctly unpivot two columns simultaneously ?

CodePudding user response：

You're better off using a VALUES table construct:

SELECT YT.StoreID,
       V.Target,
       V.TargetDate
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(Target1, Date1),
                        (Target2, Date2),
                        (Target3, Date3))V(Target,TargetDate);