Link to home
Start Free TrialLog in
Avatar of Errol Farro
Errol FarroFlag for Aruba

asked on

Pivot table for MS SQL

I have a table as per the below

I have a table as per the below and would like to create a pivot tabe using MS SQL. Note - there are about 30 GL numbers. The attached gives a better illustration of the below

Any help is appreciated.

Field             Description
mfMpid            Customer Number
mpGlno            Gl number
mpAmount      Amount booked on G/L


Example of content of file
==========================

mfMpid      mpGlno      mpAmount
1      5      200
1      7      300
1      3      700
1      5      800
2      2      300
2      4      600
3      8      100
3      9      200
3      1      400


Expected result
===============

GL            1      2      3      4      5      7      8      9
1                  700            1000      300            
2            300            600                        
3      400                                    100      200
Capture.JPG
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Feel free to modify as per your particular case.

declare @tempTable table
(
  mfMpid integer not null
 ,mpGlno integer not null
 ,mpAmount integer not null
)

insert into @tempTable values
 (1, 5, 200)
,(1, 7, 300)
,(1, 3, 700)
,(1, 5, 800)
,(2, 2, 300)
,(2, 4, 600)
,(3, 8, 100)
,(3, 9, 200)
,(3, 1, 400)

--select * from @tempTable

SELECT mfMpid, [1], [2], [3], [4], [5], [7], [8], [9]
FROM   
(SELECT mpGlno, mpAmount, mfMpid  
FROM @tempTable) tt
PIVOT  
(  
sum(mpAmount)  
FOR mpGlno IN  
( [1], [2], [3], [4], [5], [7], [8], [9] )  
) AS p  
ORDER BY p.mfMpid;  

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial