asked on

MSSQL PIVOT TABLE

Hello,
I have a query that give a result that I have to pivot.
I'm really new to pivot for mssql , so getting a little disappointed....
thanks for your help

here the query

SELECT ID_SERVICE,ID_DROIT,CODE_REVENDEUR from fn_function('8909' )
ORDER BY ORDRE_SERVICE,ORDRE_DROIT

the result, and the expected result is in the attached file.

thanks for any help .

regards.
PIVOT.xlsx

lcohan

SELECT ID_SERVICE,
            ID_DROIT,
[1] AS [1st],
[2] AS [2nd],
[3] AS [3rd],
[4] AS [4th],
[5] AS [5th],
[6] AS [6th],
            [7] AS [7th]
FROM (SELECT ID_SERVICE,ID_DROIT,CODE_REVENDEUR FROM fn_function('8909')) AS src --(1)
PIVOT (SUM(CODE_REVENDEUR) FOR ID_DROIT IN ([1],[2],[3],[4],[5],[6],[7])) AS pvt --(2)
ORDER BY ORDRE_SERVICE,ORDRE_DROIT

--or like this if you want NULLs converted to 0

--===== Use a Pivot to do the same thing we did with the Cross Tab
SELECT ID_SERVICE,
            ID_DROIT,
COALESCE([1],0) AS [1st],
COALESCE([2],0) AS [2nd],
COALESCE([3],0) AS [3rd],
COALESCE([4],0) AS [4th],
COALESCE([5],0) AS [5th],
COALESCE([6],0) AS [6th],
            COALESCE([7],0) AS [7th]
FROM (SELECT ID_SERVICE,ID_DROIT,CODE_REVENDEUR FROM fn_function('8909')) AS src --(1)
PIVOT (SUM(CODE_REVENDEUR) FOR ID_DROIT IN ([1],[2],[3],[4],[5],[6],[7])) AS pvt --(2)
ORDER BY ORDRE_SERVICE,ORDRE_DROIT

ASKER CERTIFIED SOLUTION

Surendra Nath

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sharath S

try this.

SELECT * 
  FROM Test
 PIVOT(MAX(CODE_REVENDEUR) FOR ID_DROIT IN ([1],[2],[3],[4]) ) AS P

Open in new window

http://sqlfiddle.com/#!3/61950/3

bruno_boccara

ASKER

MANY THANKS.

great solution.