SQL Pivot/Unpivot similar column names

Question

drop table if exists tbl
go
create table tbl(
refId int,
refNm varchar(10),
prodID_0 int,
prodNm_0 varchar(10),
prodDesc_0 VARCHAR(10),
prodID_1 int,
prodNm_1 varchar(10),
prodDesc_1 VARCHAR(10),
prodID_2 int,
prodNm_2 varchar(10),
prodDesc_2 VARCHAR(10)
)

insert into tbl values
(1,'aaa',11,'aaa_1_1','Prod 1_1',21,'bbb_2_1','Prod 2_1',31,'ccc_3_1','Prod 3_1'),
(2,'bbb',22,'bbb_2_2','Prod 2_2',23,'bbb_2_2','Prod 2_2',34,'ccc_3_2','Prod 3_2'),
(3,'ccc',33,'ccc_3_3','Prod 3_3',24,'bbb_2_3','Prod 2_3',35,'ccc_3_3','Prod 3_3')

select * from tbl:

refId	refNm	prodID_0	prodNm_0	prodDesc_0	prodID_1	prodNm_1	prodDesc_1	prodID_2	prodNm_2	prodDesc_2
1	aaa	11	aaa_1_1	Prod 1_1	21	bbb_2_1	Prod 2_1	31	ccc_3_1	Prod 3_1
2	bbb	22	bbb_2_2	Prod 2_2	23	bbb_2_2	Prod 2_2	34	ccc_3_2	Prod 3_2
3	ccc	33	ccc_3_3	Prod 3_3	24	bbb_2_3	Prod 2_3	35	ccc_3_3	Prod 3_3

I tried to unpivot but it wasn't giving me the desired results.

How can I get the output below, based on the table above:

refId	refNm	prodID	prodNm	prodDesc
1	aaa	11	aaa_1_1	Prod 1_1
1	aaa	21	bbb_2_1	Prod 2_1
1	aaa	31	ccc_3_1	Prod 3_1
2	bbb	22	bbb_2_2	Prod 2_2
2	bbb	23	bbb_2_2	Prod 2_2
2	bbb	34	ccc_3_2	Prod 3_2
3	ccc	33	ccc_3_3	Prod 3_3
3	ccc	24	bbb_2_3	Prod 2_3
3	ccc	35	ccc_3_3	Prod 3_3

Answer 1

You can solve it by manual pivot:

select refid, refnm, unpvt.*
from #tbl l
cross apply (
    values  (prodid_0, prodnm_0, proddesc_0)
    ,   (prodid_1, prodnm_1, proddesc_1)
    ,   (prodid_2, prodnm_2, proddesc_2)
    ) unpvt(prodid, prodNm, proddesc)

For every row in #tbl this creates 3 rows with columns lined up by their counters.

Edit, dynamic sql version:

declare @sql nvarchar(max)

select  @sql =  N'
select refid, refnm, unpvt.*
from #tbl l
cross apply (
    values  ' + 
    STRING_AGG('(' + name + ',' + 'prodnm_' + substring(name, charindex('_', name) + 1, 999) + ',' + 'proddesc_' + substring(name, charindex('_', name) + 1, 999) + ')', ',')
    + N'
) unpvt(prodid, prodNm, proddesc)
    '
FROM    tempdb.sys.columns sc -- change this to your database 
where   object_id = object_id('tempdb..#tbl') -- change this name for real table
and name like 'prodid[_]%'

exec(@sql)