Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with Pivot of data to columns

Author  Topic 

wembleybear
Yak Posting Veteran

93 Posts

Posted - 2015-04-22 : 12:42:57
I have a simple query showing customer net invoice value by month and work type. What I would like to do is to summarise the data so that for every customer I see the net invoice value for each work type for each month.

I would like to see this as:

Group, Customer, Period and then the individual work types, of which there are always 8, as columns with the net invoice value under each of them. How can I accomplish this best?

Sample code and results are below:

select [Group], [Customer], [Period], [Work_Type], Isnull([Invoiced],0) [Net] from vw_R_Damages


Group                                                        Customer                                                     Period                            Work_Type            Net
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------- -------------------- ---------------------
JARVIS CONTRACTING LIMITED JARVIS CONTRACTING LIMITED 2011-12 Breakdown / Call out 0.00
JARVIS CONTRACTING LIMITED JARVIS CONTRACTING LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT NORTHAMPTON 2011-12 Breakdown / Call out 0.00
TAYLOR WIMPEY GEORGE WIMPEY NORTH THAMES 2011-12 Breakdown / Call out 0.00
LEADBITTER DENNE CONSTRUCTION LIMITED 2011-12 Breakdown / Call out 0.00
PERSIMMON PERSIMMON HOMES (EAST MIDLANDS) LTD 2011-12 Collection Damage 17.55
MUNNELLYS SUPPORT SERVICES LIMITED MUNNELLYS SUPPORT SERVICES LIMITED 2011-12 Puncture Repair 150.00
TAYLOR WIMPEY GEORGE WIMPEY NORTH THAMES 2011-12 Breakdown / Call out 0.00
BLOOR BLOOR HOMES SOUTHERN 2011-12 Breakdown / Call out 0.00
CHELSTEEN CONSTRUCTION CHELSTEEN CONSTRUCTION 2011-12 Puncture Repair 61.50
GALLIFORD TRY IMTECH JV GALLIFORD TRY IMTECH JV 2011-12 Breakdown / Call out 0.00
BARRATT DAVID WILSON HOMES NORTH MIDLANDS 2011-12 Hose 0.00
BARRATT DAVID WILSON HOMES NORTH WEST 2012-01 Damage repair 10.98
GLADEDALE GLADEDALE (SUNDERLAND) LTD T/A BETT HOMES 2012-12 Breakdown / Call out 0.00
BARRATT DAVID WILSON HOMES NORTH WEST 2011-12 Breakdown / Call out 0.00
GLADEDALE GLADEDALE (SUNDERLAND) LTD T/A BETT HOMES 2011-12 Breakdown / Call out 0.00
MPG GROUP LIMITED MPG GROUP LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT DAVID WILSON HOMES NORTH WEST 2011-12 Breakdown / Call out 0.00
WILLMOTT DIXON HOUSING LIMITED WILLMOTT DIXON HOUSING LIMITED 2011-12 Breakdown / Call out 263.04
ISG PEARCE LIMITED ISG PEARCE LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT WARD HOMES 2011-12 Breakdown / Call out 0.00
BARRATT DAVID WILSON HOMES SOUTH MIDLANDS 2011-12 Breakdown / Call out 0.00
LEE MARLEY BRICKWORK LIMITED LEE MARLEY BRICKWORK LIMITED 2011-12 Breakdown / Call out 0.00
COUNTRYSTYLE RECYCLING LIMITED COUNTRYSTYLE RECYCLING LIMITED 2011-12 Breakdown / Call out 0.00
LEE MARLEY BRICKWORK LIMITED LEE MARLEY BRICKWORK LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT DAVID WILSON SOUTHAMPTON 2011-12 Puncture Repair 54.50
FORKRENT FORKRENT 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT EAST LONDON 2011-12 Puncture Repair 164.50
BELLWAY BELLWAY HOMES ESSEX DIVISION LTD 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT WEST LONDON-ACCOUNTS PAYABL 2011-12 Breakdown / Call out 0.00
GLADEDALE MANOR KINGDOM (SCOTLAND) LIMITED 2012-05 Damage repair 0.00
BELLWAY BELLWAY HOMES ESSEX DIVISION LTD 2011-12 Breakdown / Call out 0.00
OSBORN BRICKWORK LIMITED OSBORN BRICKWORK LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT NORTH EAST 2012-12 Breakdown / Call out 0.00
J BREHENY CONTRACTORS LTD J BREHENY CONTRACTORS LTD 2011-12 Breakdown / Call out 0.00
TAYLOR WIMPEY GEORGE WIMPEY SOUTH WEST THAMES 2011-12 Breakdown / Call out 95.00
BLOOR BLOOR HOMES SOUTHERN 2011-12 Breakdown / Call out 0.00
BLOOR BLOOR HOMES SOUTHERN 2011-12 Breakdown / Call out 0.00
TAYLOR WIMPEY GEORGE WIMPEY NORTH THAMES 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT NORTHAMPTON 2012-01 Breakdown / Call out 0.00
COUNTRYSIDE COUNTRYSIDE PROPERTIES PLC 2011-12 Damage repair 124.19
HEALTHCARE & COMMUNITY CONST. LTD HEALTHCARE & COMMUNITY CONST. LTD 2011-12 Collection Damage 80.00
BARRATT DAVID WILSON HOMES SOUTHERN 2011-12 Breakdown / Call out 0.00
HUTTON CONSTRUCTION LTD HUTTON CONSTRUCTION LTD 2011-12 Breakdown / Call out 0.00
LEADBITTER DENNE CONSTRUCTION LIMITED 2011-12 Breakdown / Call out 0.00
COWLIN CONSTRUCTION LIMITED COWLIN CONSTRUCTION LIMITED 2011-12 Breakdown / Call out 0.00
PERIQUIN LIMITED PERIQUIN LIMITED 2011-12 Breakdown / Call out 0.00
HIGGINS CONSTRUCTION PLC HIGGINS CONSTRUCTION PLC 2011-12 Breakdown / Call out 0.00
TAMDOWN PLANT HIRE LIMITED TAMDOWN PLANT HIRE LIMITED 2011-12 Breakdown / Call out 0.00
BLOOR BLOOR HOMES LTD T/AS BLOOR HOMES SOUTH WEST 2011-12 Breakdown / Call out 0.00
ACE PAVING (SUFFOLK) LIMITED ACE PAVING (SUFFOLK) LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT NORTH MIDLANDS 2011-12 Breakdown / Call out 0.00
BARRATT DAVID WILSON HOMES NORTH WEST 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT NORTH MIDLANDS 2011-12 Breakdown / Call out 0.00
D + J HODGE D + J HODGE 2011-12 Breakdown / Call out 0.00
JEHU PROJECT SERVICES LIMITED JEHU PROJECT SERVICES LIMITED 2011-12 Breakdown / Call out 0.00
GLADEDALE BETT HOMES LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT DAVID WILSON SOUTHAMPTON 2011-12 Breakdown / Call out 0.00
BARRATT DAVID WILSON HOMES SOUTH MIDLANDS 2011-12 Breakdown / Call out 0.00
PERSIMMON PERSIMMON HOMES (ESSEX) LTD 2011-12 Breakdown / Call out 0.00
FREEDOM FINANCE FREEDOM FINANCE 2011-12 Breakdown / Call out 128.59
FREEDOM FINANCE FREEDOM FINANCE 2011-12 Breakdown / Call out 0.00
BOWMER & KIRLAND LIMITED BOWMER & KIRLAND LIMITED 2011-12 Collection Damage 84.00
QUADRANT CONSTRUCTION SERVICES LTD QUADRANT CONSTRUCTION SERVICES LTD 2012-01 Damage repair 79.13
BARRATT DAVID WILSON HOMES SOUTH MIDLANDS 2011-12 Breakdown / Call out 0.00
BALFOUR MANSELL CONSTRUCTION SERVICES LTD 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT EXETER 2012-01 Puncture Repair 0.00
HOPKINS HOMES LTD HOPKINS HOMES LTD 2011-12 Breakdown / Call out 0.00
BARRATT DAVID WILSON HOMES EAST MIDLANDS 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT EXETER 2011-12 Puncture Repair 54.50
HILL PARTNERSHIPS HILL PARTNERSHIPS LTD 2011-12 Breakdown / Call out 0.00
J BREHENY CONTRACTORS LTD J BREHENY CONTRACTORS LTD 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT SOUTHERN COUNTIES 2011-12 Puncture Repair 273.50
BARRATT BDW YORKSHIRE EAST 2012-02 Glass 0.00
FLAHIVE BRICKWORK LIMITED FLAHIVE BRICKWORK LIMITED 2011-12 Breakdown / Call out 0.00
LEADBITTER DENNE CONSTRUCTION LIMITED 2011-12 Damage repair 430.83
BARRATT BARRATT SOUTH WALES 2011-12 Breakdown / Call out 0.00
BERKELEY / ST JAMES BERKELEY HOMES (CAPITAL) PLC 2011-12 Breakdown / Call out 0.00
MUNNELLYS SUPPORT SERVICES LIMITED MUNNELLYS SUPPORT SERVICES LIMITED 2011-12 Breakdown / Call out 0.00
BERKELEY / ST JAMES BERKELEY HOMES (CAPITAL) PLC 2011-12 Breakdown / Call out 0.00
MIDDLETON AGGREGATES LIMITED MIDDLETON AGGREGATES LIMITED 2011-12 Breakdown / Call out 0.00
BLOOR BLOOR HOMES SOUTHERN 2011-12 Breakdown / Call out 0.00
COWLIN CONSTRUCTION LIMITED COWLIN CONSTRUCTION LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT NORTHAMPTON 2011-11 Breakdown / Call out 0.00
NICHOLSON (NEEDHAM) LIMITED NICHOLSON (NEEDHAM) LIMITED 2011-12 Collection Damage 17.55
ALLPRESS FARMS LIMITED ALLPRESS FARMS LIMITED 2011-12 Breakdown / Call out 0.00
EXCEL BRICKWORK LIMITED EXCEL BRICKWORK LIMITED 2011-12 Breakdown / Call out 0.00
BARRATT BARRATT SOUTH WALES 2012-01 Glass 218.88


Many thanks
Martyn

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-22 : 13:31:17
[code]SELECT * FROM
( select [Group], [Customer], [Period], [Work_Type], Isnull([Invoiced],0) [Net] from vw_R_Damages ) a
PIVOT (SUM([Net]) FOR [Work_Type] IN ([Breakdown / Call out], [ Collection Damag] /*, other worktypes here */ )) P
[/code]
Go to Top of Page
   

- Advertisement -