Complex unpivot from sql table

Question

I got a table with all sort of columns. Lets say something like this :

Name | Model | year1 | group1 | year2 | group2 | year3 | group3 | Year4 | group4

All of the rows got data in Name and Model.

All of the rows got data in year1 and group1,

Some of the rows got data in year1 group1 and year2 group2,

Some of the rows got data in year1 group 1 and year2 group2 and year3 group3,

etc...

It is not possible to have data in a higher number group without having values in the lower one. Example : cant have data in year3 group3 without having data in 2 and 1 as well.

My problem is as follow,

I need to unpivot all of those values into separate rows. So that the final result will be something like this :

Name | Model | year | group

P.S. If for example year4 group4 columns have no values in them then they are defined as NULL.

I am using MSSQL server 2016.

Answer 1

You could use this

SELECT name, model, year1 AS [year], 'group1' AS [group]
FROM table_name
WHERE group1 IS NOT NULL
UNION ALL
SELECT name, model, year2, 'group2'
FROM table_name
WHERE group2 IS NOT NULL
UNION ALL
SELECT name, model, year3, 'group3'
FROM table_name
WHERE group3 IS NOT NULL
UNION ALL
SELECT name, model, year4, 'group4'
FROM table_name
WHERE group4 IS NOT NULL;

And you may consider adding a column in all 4 SELECT statement for value of each group: groupx AS value

SELECT name, model, year1 AS [year], group1 AS value, 'group1' AS [group]
FROM table_name
WHERE group1 IS NOT NULL
UNION ALL
SELECT name, model, year2, group2, 'group2'
FROM table_name
WHERE group2 IS NOT NULL
UNION ALL
SELECT name, model, year3, group3, 'group3'
FROM table_name
WHERE group3 IS NOT NULL
UNION ALL
SELECT name, model, year4, group4, 'group4'
FROM table_name
WHERE group4 IS NOT NULL;