SQL Server 2014

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

8 rows affected

SELECT DISTINCT ST2.DeckName, 
    SUBSTRING(
        (
            SELECT ';'+DeckId  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [DeckId],
        
      SUBSTRING(
        (
            SELECT ';'+Location  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [Location]  ,
        
      SUBSTRING(
        (
            SELECT ';'+Items  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [Items]  , 
        
      SUBSTRING(
        (
            SELECT ';'+Places  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [Places]  ,
        
     SUBSTRING(
        (
            SELECT ';'+UserName  AS [text()]
            FROM test_tbl ST1
            WHERE ST1.DeckName = ST2.DeckName
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 9999) [UserName]  
        
FROM test_tbl ST2

DeckName	DeckId	Location	Items	Places	UserName
NOVA	XK001;XK002;XK001	USA;IND;USA	Cream;DEO;Cosmetic	ASIA-P;AFRICA;ASIA-P	NOVA001;NOVA002;NOVA003
PHLIPS	NS001	USA	Cream	ASIA-P	PHLPS001
RUDS	PS001	USA	Cream	ASIA-P	VLT001
VEET	VK001;VK003;VK002	RUS;CHI;NED	PROFIX;Cream;WAX	UK;ASIA-P;RUSSIA	VEET001;VEET002;VEET001