Consolidate records - SQL2008R2

[Jezza]

I have been tasked with backward engineering a financial report from a system that is being decommissioned, the presentation layer shows the monthly actuals for a cost code all in one row. In the image you can see that there were 5 monthly payments in 2013 (2013/1 = April, 2013/5 = August)

I would like to present this so that there is one row with just all the monthly values instead of 5 rows, is that possible?

BTW I created this extract by using the following code:

Code: Select all

...

COALESCE(MAX(CASE WHEN BAL.period = '1' THEN BAL.full_value END),0) ' Actual P1',
COALESCE(MAX(CASE WHEN BAL.period = '2' THEN BAL.full_value END),0) ' Actual P2',
COALESCE(MAX(CASE WHEN BAL.period = '3' THEN BAL.full_value END),0) ' Actual P3',
COALESCE(MAX(CASE WHEN BAL.period = '4' THEN BAL.full_value END),0) ' Actual P4',
COALESCE(MAX(CASE WHEN BAL.period = '5' THEN BAL.full_value END),0) ' Actual P5',
COALESCE(MAX(CASE WHEN BAL.period = '6' THEN BAL.full_value END),0) ' Actual P6'

...

[HansV]

Would PIVOT help here? See for example

Simple Way To Use Pivot In SQL Query
Pivot tables in SQL Server. A simple sample.

[Jezza]

Would PIVOT help here? See for example

Simple Way To Use Pivot In SQL Query
Pivot tables in SQL Server. A simple sample.

Thanks Hans,

I had to d a complete rework on the data as there were a few joins in the original code and I returned to columnar data instead of aggregated. I found it easier to insert the resulting code into a temporary table and then the PIVOT worked like a dream.

As an aside, although this is on a SQL2008R2 server it was originally imported from a SQL2005 server and had to raise the compatibility from 70 to 90 to get PIVOT to work (Yes, some of the databases are as old as some of the exhibits we have at work )