Is Dyamic Pivot used for this?

Question

I have a table below. (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 such a result.

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

Crashed all over the world and it seems that PIVOT is what I need to do. However, I am not sure how this can be implemented for my tables. It would be very helpful to help if anyone can help me. Thanks!

Answer 1

I will create a second answer, as this approach is different from my first:

This dynamic query will first find the maximum number of a single identifier, and then build a dynamic vault

 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; 

Result

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

Answer 2

This is a sentence with a concatenated result:

 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') ,('2CS','C435664C','Grace','Norway',NULL) ,('3TG','G885595H','Rae',NULL,'Japan'); SELECT ID,IC,Name,UGCOS, ( STUFF( ( SELECT ' ,' + x.MCOS FROM #tmpTbl AS x WHERE x.ID=outerTbl.ID FOR XML PATH('') ),1,2,'' ) ) AS MCOS FROM #tmpTbl AS outerTbl GROUP BY ID,IC,Name,UGCOS; GO DROP TABLE #tmpTbl; 

Result

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

Answer 3

Using Cross Apply and Pivot, we can achieve this.

  DECLARE @Table1 TABLE ( ID varchar(3), IC varchar(8), Name varchar(6), UGCOS varchar(9), MCOS varchar(7)) ; INSERT INTO @Table1 ( ID , IC , Name , UGCOS , MCOS ) VALUES ('1AA', 'A123456B', 'Edmund', 'Australia', 'Denmark'), ('1AA', 'A123456B', 'Edmund', 'Australia', 'France'), ('2CS', 'C435664C', 'Grace', 'Norway', NULL), ('3TG', 'G885595H', 'Rae', NULL, 'Japan') ; Select ID , IC , Name , UGCOS,MAX([MCOS1])[MCOS1],MAX([MCOS2])[MCOS2] from ( select ID , IC , Name , UGCOS , MCOS,col,val,col +''+CAST(ROW_NUMBER()OVER(PARTITION BY ID ORDER BY col) AS VARCHAR)RN from @Table1 CROSS APPLY (values('MCOS',MCOS))CS(col,val))T PIVOT (MAX(val) FOR RN IN ([MCOS1],[MCOS2]))PVT GROUP BY ID , IC , Name , UGCOS 

Answer 4

Do you always have a maximum of 2 rows of data that you want to turn into columns? If so, it would make you;

 CREATE TABLE #TableName (ID varchar(3), IC varchar(8), Name varchar(6), UCGOS varchar(9), MCOS varchar(7)) INSERT INTO #TableName VALUES ('1AA','A123456B','Edmund','Australia','Denmark') ,('1AA','A123456B','Edmund','Australia','France') ,('2CS','C435664C','Grace','Norway',NULL) ,('3TG','G885595H','Rae',NULL,'Japan') SELECT DISTINCT a.ID ,a.IC ,a.NAME ,a.UCGOS ,b.Mcos1 MCOS ,c.Mcos2 MCOS1 FROM #TableName a LEFT JOIN ( SELECT ID ,MAX(MCOS) Mcos1 FROM #TableName GROUP BY ID ) b ON a.ID = b.ID LEFT JOIN ( SELECT ID ,MIN(MCOS) Mcos2 FROM #TableName GROUP BY ID ) c ON a.ID = c.ID AND ( b.ID = c.ID AND b.Mcos1 <> c.Mcos2 ) DROP TABLE #TableName 

Gives you the result.