SQL Server 2022

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

CREATE TABLE [dbo].[WF1_DATA] ([SECTOR] [nvarchar](8) NULL,
                               [PROGRAM] [nvarchar](22) NULL,
                               [CAWPID] [int] NULL,
                               [MANAGER] [nvarchar](59) NULL,
                               [CA1_ID] [nvarchar](10) NULL,
                               [CA1_BD] [nvarchar](22) NULL,
                               [CA1] [nvarchar](59) NULL,
                               [CA1_DESC] [nvarchar](254) NULL,
                               [CA2_ID] [nvarchar](10) NULL,
                               [CA2_BD] [nvarchar](22) NULL,
                               [CA2] [nvarchar](59) NULL,
                               [CA2_DESC] [nvarchar](254) NULL,
                               [CA3_ID] [nvarchar](10) NULL,
                               [CA3_BD] [nvarchar](22) NULL,
                               [CA3] [nvarchar](59) NULL,
                               [CA3_DESC] [nvarchar](254) NULL,
                               [WP_ID] [nvarchar](20) NULL,
                               [WP] [nvarchar](59) NULL,
                               [DESCRIP] [nvarchar](254) NULL, ) ON [PRIMARY];
INSERT [dbo].[WF1_DATA] ([SECTOR],
                         [PROGRAM],
                         [CAWPID],
                         [MANAGER],
                         [CA1_ID],
                         [CA1_BD],
                         [CA1],
                         [CA1_DESC],
                         [CA2_ID],
                         [CA2_BD],
                         [CA2],
                         [CA2_DESC],
                         [CA3_ID],
                         [CA3_BD],
                         [CA3],
                         [CA3_DESC],
                         [WP_ID],
                         [WP],
                         [DESCRIP])
VALUES (N'SECTOR1', N'TEST1', -2074554109, N' ', N'CA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'', N'', N'', N'', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR49', N'Production 1100'),
       (N'SECTOR1', N'TEST1', -2074554109, N' ', N'CA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'', N'', N'', N'', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR49', N'Production 1100'),
       (N'SECTOR1', N'TEST1', -2074554109, N' ', N'CA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'', N'', N'', N'', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR49', N'Production 1100'),
       (N'SECTOR1', N'TESTA', 2074554110, N' ', N'', N'', N'', N'', N'CA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR50', N'Production 2100'),
       (N'SECTOR1', N'TESTA', 2074554110, N' ', N'', N'', N'', N'', N'CA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR50', N'Production 2100'),
       (N'SECTOR1', N'TESTA', 2074554110, N' ', N'', N'', N'', N'', N'CA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR50', N'Production 2100'),
       (N'SECTOR1', N'TESTA', 2074554110, N' ', N'EA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'DA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR51', N'Production 3100'),
       (N'SECTOR1', N'TESTA', 2074554110, N' ', N'EA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'DA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR51', N'Production 3100'),
       (N'SECTOR1', N'TESTA', 2074554110, N' ', N'EA', N'TEST1_WBS', N'121-ATP', N'Test and Checkout', N'DA', N'TESTA_WBS', N'122-ATP', N'Checkout', N' ', N' ', N' ', NULL, N'WP', N'121-ATP-DLBR51', N'Production3100');
9 rows affected

SELECT *
FROM dbo.WF1_DATA;


SECTOR
PROGRAM
CAWPID
MANAGER
CA1_ID
CA1_BD
CA1
CA1_DESC
CA2_ID
CA2_BD
CA2
CA2_DESC
CA3_ID
CA3_BD
CA3
CA3_DESC
WP_ID
WP
DESCRIP


SECTOR1
TEST1
-2074554109
 
CA
TEST1_WBS
121-ATP
Test and Checkout




 
 
 
null
WP
121-ATP-DLBR49
Production 1100

SECTOR1
TEST1
-2074554109
 
CA
TEST1_WBS
121-ATP
Test and Checkout




 
 
 
null
WP
121-ATP-DLBR49
Production 1100

SECTOR1
TEST1
-2074554109
 
CA
TEST1_WBS
121-ATP
Test and Checkout




 
 
 
null
WP
121-ATP-DLBR49
Production 1100

SECTOR1
TESTA
2074554110
 




CA
TESTA_WBS
122-ATP
Checkout
 
 
 
null
WP
121-ATP-DLBR50
Production 2100

SECTOR1
TESTA
2074554110
 




CA
TESTA_WBS
122-ATP
Checkout
 
 
 
null
WP
121-ATP-DLBR50
Production 2100

SECTOR1
TESTA
2074554110
 




CA
TESTA_WBS
122-ATP
Checkout
 
 
 
null
WP
121-ATP-DLBR50
Production 2100

SECTOR1
TESTA
2074554110
 
EA
TEST1_WBS
121-ATP
Test and Checkout
DA
TESTA_WBS
122-ATP
Checkout
 
 
 
null
WP
121-ATP-DLBR51
Production 3100

SECTOR1
TESTA
2074554110
 
EA
TEST1_WBS
121-ATP
Test and Checkout
DA
TESTA_WBS
122-ATP
Checkout
 
 
 
null
WP
121-ATP-DLBR51
Production 3100

SECTOR1
TESTA
2074554110
 
EA
TEST1_WBS
121-ATP
Test and Checkout
DA
TESTA_WBS
122-ATP
Checkout
 
 
 
null
WP
121-ATP-DLBR51
Production3100


SELECT WD.SECTOR AS Sector,
       WD.PROGRAM AS ProgramID,
       CASE 'CA'
           WHEN WD.CA1_ID THEN WD.CA1
           WHEN WD.CA2_ID THEN WD.CA2
           WHEN WD.CA3_ID THEN WD.CA3
           ELSE CONCAT_WS(',',WD.CA1, WD.CA2, WD.CA3)
       END AS ControlAccount,
       CASE 'CA'
           WHEN WD.CA1_ID THEN WD.CA1_DESC
           WHEN WD.CA2_ID THEN WD.CA2_DESC
           WHEN WD.CA3_ID THEN WD.CA3_DESC
           ELSE CONCAT_WS(',',WD.CA1_DESC, WD.CA2_DESC, WD.CA3_DESC)
       END AS ControlAccountDescription,
       WD.WP AS WorkPackage,
       WD.DESCRIP AS WorkPackageDescription
FROM dbo.WF1_DATA WD;


Sector
ProgramID
ControlAccount
ControlAccountDescription
WorkPackage
WorkPackageDescription


SECTOR1
TEST1
121-ATP
Test and Checkout
121-ATP-DLBR49
Production 1100

SECTOR1
TEST1
121-ATP
Test and Checkout
121-ATP-DLBR49
Production 1100

SECTOR1
TEST1
121-ATP
Test and Checkout
121-ATP-DLBR49
Production 1100

SECTOR1
TESTA
122-ATP
Checkout
121-ATP-DLBR50
Production 2100

SECTOR1
TESTA
122-ATP
Checkout
121-ATP-DLBR50
Production 2100

SECTOR1
TESTA
122-ATP
Checkout
121-ATP-DLBR50
Production 2100

SECTOR1
TESTA
121-ATP,122-ATP,
Test and Checkout,Checkout
121-ATP-DLBR51
Production 3100

SECTOR1
TESTA
121-ATP,122-ATP,
Test and Checkout,Checkout
121-ATP-DLBR51
Production 3100

SECTOR1
TESTA
121-ATP,122-ATP,
Test and Checkout,Checkout
121-ATP-DLBR51
Production3100


DROP TABLE dbo.WF1_DATA;

SECTOR	PROGRAM	CAWPID	CA1_ID	CA1_BD	CA1	CA1_DESC	CA2_ID	CA2_BD	CA2	CA2_DESC	CA3_DESC	WP_ID	WP	DESCRIP
SECTOR1	TEST1	-2074554109	CA	TEST1_WBS	121-ATP	Test and Checkout					null	WP	121-ATP-DLBR49	Production 1100
SECTOR1	TEST1	-2074554109	CA	TEST1_WBS	121-ATP	Test and Checkout					null	WP	121-ATP-DLBR49	Production 1100
SECTOR1	TEST1	-2074554109	CA	TEST1_WBS	121-ATP	Test and Checkout					null	WP	121-ATP-DLBR49	Production 1100
SECTOR1	TESTA	2074554110					CA	TESTA_WBS	122-ATP	Checkout	null	WP	121-ATP-DLBR50	Production 2100
SECTOR1	TESTA	2074554110					CA	TESTA_WBS	122-ATP	Checkout	null	WP	121-ATP-DLBR50	Production 2100
SECTOR1	TESTA	2074554110					CA	TESTA_WBS	122-ATP	Checkout	null	WP	121-ATP-DLBR50	Production 2100
SECTOR1	TESTA	2074554110	EA	TEST1_WBS	121-ATP	Test and Checkout	DA	TESTA_WBS	122-ATP	Checkout	null	WP	121-ATP-DLBR51	Production 3100
SECTOR1	TESTA	2074554110	EA	TEST1_WBS	121-ATP	Test and Checkout	DA	TESTA_WBS	122-ATP	Checkout	null	WP	121-ATP-DLBR51	Production 3100
SECTOR1	TESTA	2074554110	EA	TEST1_WBS	121-ATP	Test and Checkout	DA	TESTA_WBS	122-ATP	Checkout	null	WP	121-ATP-DLBR51	Production3100