SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Test
(
  id int identity(1, 1) PRIMARY KEY
, a int NULL
, b int NULL
, c int NULL
);
INSERT INTO Test (a, b, c)
VALUES
  (1, 1, 1)
, (2, NULL, 2)
, (NULL, 3, 3)
, (NULL, 4, NULL)
, (5, NULL, 5)
, (NULL, 6, 6);
6 rows affected

SELECT *
FROM Test;


id
a
b
c


1
1
1
1

2
2
null
2

3
null
3
3

4
null
4
null

5
5
null
5

6
null
6
6


SELECT *
FROM 
  Test
UNPIVOT
(
  val
  FOR col
  IN ([a], [b], [c])
) u


id
val
col


1
1
a

1
1
b

1
1
c

2
2
a

2
2
c

3
3
b

3
3
c

4
4
b

5
5
a

5
5
c

6
6
b

6
6
c

id	a	b	c
1	1	1	1
2	2	null	2
3	null	3	3
4	null	4	null
5	5	null	5
6	null	6	6