clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249313 fiddles created (32226 in the last week).

-- Original content import CREATE TABLE ##tablename ( [nopas] VARCHAR(8), [code] VARCHAR(4), [name] VARCHAR(64), [order] SMALLINT ); INSERT INTO ##tablename ([nopas], [code], [name], [order]) VALUES ('00115301','D031','ADAM', 1) ,('00130015','D031','ADAM', 2) ,('00149998','D026','JAMES', 1) ,('00149970','D028','FIKI', 1) ,('00142641','D028','FIKI', 2) ,('00127700','D028','FIKI', 3);
6 rows affected
 hidden batch(es)


SELECT IDENTITY(INT, 1,1) as [id], [name] INTO #temp FROM ##tablename GROUP BY [name];
3 rows affected
 hidden batch(es)


-- Create the dynamic SQL DECLARE @count INT = 1; DECLARE @max INT = (SELECT MAX(id) FROM #temp); DECLARE @dynSQL VARCHAR(2048) = 'SELECT * INTO ##newglobaltable -- SELECT the result from the dynamic SQL into temporary table FROM (SELECT [order], [name], [nopas] FROM ##tablename) AS sourceTable PIVOT( MAX([nopas]) FOR [name] IN ('; SET @count = 1; WHILE(@count <= @max) BEGIN SET @dynSQL += '[' + (SELECT [name] FROM #temp WHERE id = @count) + '],'; SET @count += 1; END SET @dynSQL = LEFT(@dynSQL, DATALENGTH(@dynSQL) -1) + ') ) AS pivotTable;'; PRINT @dynSQL; -- View the structure of the dynamic SQL EXEC (@dynSQL); -- Execute the dynamic SQL
SELECT * INTO ##newglobaltable -- SELECT the result from the dynamic SQL into temporary table FROM (SELECT [order], [name], [nopas] FROM ##tablename) AS sourceTable PIVOT( MAX([nopas]) FOR [name] IN ([ADAM],[FIKI],[JAMES]) ) AS pivotTable; 3 rows affected
 hidden batch(es)


-- DROP the temporary tables DROP TABLE ##tablename; DROP TABLE #temp;
 hidden batch(es)


ALTER TABLE ##newglobaltable DROP COLUMN [order]; -- DROP the [order] column
 hidden batch(es)


SELECT * FROM ##newglobaltable; -- See the result
ADAM FIKI JAMES
00115301 00149970 00149998
00130015 00142641
00127700
 hidden batch(es)