Hey Guys,

Sorry for providing in incorrect format.

Please find the below.

With MyCTE( Code,[Desc])

AS

(

SELECT 'A1','AAAA BBBB CCCC' UNION ALL

SELECT 'A1.1','AAAA BBBB CCCC DDDDD' UNION ALL

SELECT 'A1.2','AAAA BBBB CCCC DDDDD EEEEE' UNION ALL

SELECT 'A2','XXXX' UNION ALL

SELECT 'A2.1','XXXX YYYY' UNION ALL

SELECT 'A2.3','XXXX YYYY ZZZZ'

)

select * From MyCTE

;With MyCTEOutput( Code,[Desc],[Desc1],Desc2,Desc3)

AS

(

SELECT 'A1','AAAA BBBB CCCC','AAAA BBBB CCCC','','' UNION ALL

SELECT 'A1.1','AAAA BBBB CCCC DDDDD','AAAA BBBB CCCC','DDDDD','' UNION ALL

SELECT 'A1.2','AAAA BBBB CCCC DDDDD EEEEE', 'AAAA BBBB CCCC','DDDDD','EEEEE' UNION ALL

SELECT 'A2','XXXX','','','' UNION ALL

SELECT 'A2.1','XXXX YYYY','XXXX','YYYY','' UNION ALL

SELECT 'A2.3','XXXX YYYY ZZZZ','XXXX','YYYY','ZZZZ'

)

SELECT * FROM MyCTEOutput

Basically, we need to keep the common word in all the rows.

Thanks,

Karthik