How to PIVOT data from MSSQL query and Fetch to PHP view I have a MSSQL 2005 Database with query content like this :




nopas
code
name
order




00115301
D031
ADAM
1


00130015
D031
ADAM
2


00149998
D026
JAMES
1


00149970
D028
FIKI
1


00142641
D028
FIKI
2


00127700
D028
FIKI
3




For report reason, I need Output a table like :




ADAM
JAMES
FIKI
...(dynamic content based new record)




00115301
00149998
00149970
...


00130015
-
00142641
...


-
-
00127700
...




Can anyone help me??
You can do a pivot with CASE statements. It's basically the manual way of pivoting and if you were using a version of SQL Server before 2005, you would need to go that route. SQL Server 2005 added the PIVOT function.
Because you need it to be dynamic, you will need to use dynamic SQL. It's a lot of leg work but definitely doable.
I'm going to create your table content in a global temporary table (has to be global to work in dynamic SQL).
-- CREATE GLOBAL TEMPORARY TABLE
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);

Next, I'm going to SELECT just the unique names into another temporary table and associate them with an IDENTITY with which I can iterate through in a for loop.
SELECT IDENTITY(INT, 1,1) as [id], [name] 
INTO #temp
FROM ##tablename
GROUP BY [name];

Now comes the difficult part. The PRINT @dynSQL generates what I'm trying to achieve dynamically (below). We need to SELECT the [order], [name], and [nopas] for the final result. We are pivoting on the [name] in the second part of the statement, and aggregating by MAX([nopas]); but because we contain an [order] grouping in your original table content, it will only retrieve the MAX([nopas]) based on the MAX value of a single row. So, the result is that it removes aggregation. If you did not have an [order] column in your original content, we would need to use the ROW_NUMBER() function* PARTITIONED BY the [name] to generate that [order].
EDIT: I discovered that the ROW_NUMBER() function is only available in SQL Server 2012 and newer.
-- PRINT @dynSQL result
SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN ([ADAM],[FIKI],[JAMES])
) AS pivotTable;

-- The leg work to create the dynamic SQL.
DECLARE @count INT = 1;
DECLARE @max INT = (SELECT MAX(id) FROM #temp);
DECLARE @dynSQL VARCHAR(2048) = 'SELECT * FROM
(SELECT [order], [name], [nopas]
  FROM ##tablename) AS sourceTable
PIVOT(
MAX([nopas])
FOR [name] IN (';

SET @count = 1;
WHILE(@count &lt;= @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;
EXEC (@dynSQL);

DROP TABLE ##tablename;
DROP TABLE #temp;


Oh, if you want to remove the [order] column, from the result. You could  use SELECT * INTO ##newGlobalTable in the dynamic SQL and then ALTER the new table and DROP the column.