Pivot table for MS SQL

Errol Farro
Errol Farro used Ask the Experts™
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Senior Developer
E.g.

DECLARE @Sample TABLE (
    mfMpid INT ,
    mpGlno INT ,
    mpAmount INT
);

INSERT INTO @Sample ( mfMpid ,
                      mpGlno ,
                      mpAmount )
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   @Sample S
    PIVOT (   SUM(mpAmount)
              FOR mpGlno IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23] ,
                              [24] , [25], [26], [27], [28], [29], [30] )) P;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial