2

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??

1
1

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. See @Andriy M's comment below.

-- 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 <= @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.

2
  • @pde, I've put the code, from this answer, into dbfiddle with the extra steps to remove the [order] column. – Jason Dec 4 '20 at 11:28
  • 1
    I don't know why you thought ROW_NUMBER() became available only in SQL Server 2012, I'm guessing it's because that was the oldest version for which online documentation was available at the time of your post (and now the oldest one is 2016). Anyway, ROW_NUMBER has actually been available since SQL Server 2005, so it would work for the OP no problem. – Andriy M Jun 20 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.