T-SQL query table to re-sort data

Question

I am trying to come up with a means of translating the following data:

ID	F1	F2	F3	F1%	F2%	F3%
ID1	ID1F1Value	ID1F2Value	ID1F3Value	ID1F1%Value	ID1F2%Value	ID1F3%Value
ID2	ID2F1Value	ID2F2Value	ID2F3Value	ID2F1%Value	ID2F2%Value	ID2F3%Value

into the following

ID1	F	%
ID1	ID1F1Value	ID1F1%Value
ID1	ID1F2Value	ID1F2%Value
ID1	ID1F3Value	ID1F3%Value
ID2	ID2F1Value	ID2F1%Value
ID2	ID2F2Value	ID2F2%Value
ID2	ID2F3Value	ID2F3%Value

I am considering exporting it to EXCEL to run a VBA to process it after hours of trying to figure this out using SQL... Hoping that a SQL master could enlighten me with an amazing solution.

Thank you very much !

score 3 · Answer 1 · answered Jun 21 '21 at 12:20

3

You are looking for an unpivot not a pivot.

You can use CROSS APPLY (VALUES to unpivot.

SELECT
   t.ID,
   v.F,
   v.[%]
FROM YourTable t
CROSS APPLY (VALUES
    (F1, [F1%]),
    (F2, [F2%]),
    (F3, [F3%])
) v (F, [%]);

You could also use the UNPIVOT operator in some situations but it can be less flexible.

answered Jun 21 '21 at 12:20

Charlieface

52,284
6
19
43

Thank you very much. Another amazing solution ! – HotelLima Jun 23 '21 at 04:36

score 2 · Answer 2 · answered Jun 21 '21 at 12:19

2

One approach uses a union all:

SELECT ID AS ID1, F1 AS F, [F1%] AS [%]
UNION ALL
SELECT ID, F2, [F2%]
UNION ALL
SELECT ID, F3, [F3%];

answered Jun 21 '21 at 12:19

Tim Biegeleisen

502,043
27
286
360

1

Why scan the table three times? – Charlieface Jun 21 '21 at 12:20
This is amazing ! So simple and elegant! I have a long way to go in my SQL journey. – HotelLima Jun 23 '21 at 04:20

T-SQL query table to re-sort data

2 Answers2