Use Dyamic Pivot query for this?

Question

i have the below table. (no primary key in this table)

ID   |    IC      |    Name   |     UGCOS   |  MCOS     
---------------------------------------------------------
1AA  |  A123456B  |  Edmund   |  Australia  |  Denmark    
1AA  |  A123456B  |  Edmund   |  Australia  |  France     
2CS  |  C435664C  |  Grace    |  Norway     |  NULL       
3TG  |  G885595H  |  Rae      |  NULL       |  Japan    

I need to get the result like this.

ID   |    IC      |    Name   |     UGCOS   |  MCOS     |  MCOS1   
--------------------------------------------------------------------
1AA  |  A123456B  |  Edmund   |  Australia  |  Denmark  |  France
2CS  |  C435664C  |  Grace    |  Norway     |  NULL     |  NULL
3TG  |  G885595H  |  Rae      |  NULL       |  Japan    |  NULL

Did googled around and seems like PIVOT is what i need to do that. However i am not sure how can that be implemented to my tables. It would be great help if somebody can help me with it. Thanks!

Answer 1

I'll create a second answer, as this approach is something completely different from my first:

This dynamic query will first find the max count of a distinct ID and then build a dynamic pivot

CREATE TABLE #tmpTbl (ID VARCHAR(100),IC VARCHAR(100),Name VARCHAR(100),UGCOS VARCHAR(100),MCOS VARCHAR(100))  
INSERT INTO #tmpTbl VALUES
 ('1AA','A123456B','Edmund','Australia','Denmark')    
,('1AA','A123456B','Edmund','Australia','France')   
,('1AA','A123456B','Edmund','Australia','OneMore')   
,('2CS','C435664C','Grace','Norway',NULL)     
,('3TG','G885595H','Rae',NULL,'Japan');
GO

DECLARE @maxCount INT=(SELECT TOP 1 COUNT(*) FROM #tmpTbl GROUP BY ID ORDER BY COUNT(ID) DESC);
DECLARE @colNames VARCHAR(MAX)=
(
    STUFF
    (
        (
        SELECT TOP(@maxCount)
               ',MCOS' + CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS VARCHAR(10))
        FROM sys.objects --take any large table or - better! - an numbers table or a tally CTE
        FOR XML PATH('')
        ),1,1,''
    )
);

DECLARE @cmd VARCHAR(MAX)=
'SELECT p.*
FROM
(
    SELECT *
          ,''MCOS'' + CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY (SELECT NULL)) AS VARCHAR(10)) AS colName 
    FROM #tmpTbl
) AS tbl
PIVOT
(
    MIN(MCOS) FOR colName IN(' + @colNames + ')
) AS p';

EXEC(@cmd);
GO

DROP TABLE #tmpTbl;

The result

1AA A123456B    Edmund  Australia   Denmark France  OneMore
2CS C435664C    Grace   Norway      NULL    NULL    NULL
3TG G885595H    Rae     NULL        Japan   NULL    NULL