By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
X1AB_name VARCHAR(30) NOT NULL
,X1AB_age VARCHAR(30) NOT NULL
,Y2AL_name VARCHAR(30) NOT NULL
,Y2AL_age VARCHAR(30) NOT NULL
);
INSERT INTO mytable
(X1AB_name,X1AB_age,Y2AL_name,Y2AL_age) VALUES
('Todd','10','Brad','20');
1 rows affected
select *
from
(
select
PARSENAME(REPLACE(indicatorname,'_','.'),2) 'id',
PARSENAME(REPLACE(indicatorname,'_','.'),1) 'name2',
indicatorvalue
from mytable
unpivot
(
indicatorvalue
for indicatorname in ([X1AB_name]
,[X1AB_age]
,[Y2AL_name]
,[Y2AL_age]
)
) unpiv
) src
pivot
(
max(indicatorvalue)
for name2 in ([name], [age])
) piv;
| id | name | age |
|---|---|---|
| X1AB | Todd | 10 |
| Y2AL | Brad | 20 |