Transpose all columns to rows based on a column without PIVOT

Question

I am working on a report in which all columns need to be transposed to rows based on a column.

CREATE TABLE TempTable(
  Company VARCHAR(5), 
  ProcessDate DATETIME, 
  OpExp DECIMAL, 
  Tax DECIMAL, 
  Total DECIMAL);

INSERT INTO TempTable VALUES
('Comp1', getdate(), 1000, 100, 1100),
('Comp1', dateadd(year, -1, getdate()), 2000, 200, 2200),
('Comp1', dateadd(year, -2, getdate()), 3000, 300, 3300);

SELECT * FROM TempTable;

But for report, I have to transpose this table into

Here the columns 2015, 2016, 2017 are dynamic which are based on 'ProcessDate' year.

I tried with PIVOT but it throws

Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE

As the database is already in production, it is not possible to alter the compatibility level.

I tried with UNION ALL, CASE as suggested in

Simple way to transpose columns and rows in Sql?

But column data types are different and cannot use aggregate functions as the result must have all rows.

Is there any way to convert the columns to rows? Or Is it possible to generate this report using SSRS instead of RDLC?

Answer 1

Here is the SSRS only way which is probably the best way to do it...

I've extended your sample data a little in this example to include 9 rows, 3 rows for 3 companies.

Create a new blank report and add your datasets as normal. Then insert a matrix control on the report. Drag the fields as shown in the diagram below to initially setup the matrix. We will edit this but it gives us a quick start.

NOTE: Step 3 should read "Drag ProcessDate here..."

Now we need to chnage from process date to just the year so right-click the ProcessDate cell from step3 and click 'expression'. Set the expression to

=YEAR(Fields!ProcessDate.Value)

Under the report design in the group designer pane, right-click on the [ProcessDate] column group and choose 'group properties' and set the Group On property to the same expression =YEAR(Fields!ProcessDate.Value)

Now back in the report design, right-click the data cell (from step 4 above) and do 'Insert Row' -> 'Inside Group - Below'

Repeat this process to add a 3rd row.

In the two new blank cells, click the field list drop down and choose Tax and Total respectively.

If you want to add a caption column. Right-click the cell with [Company] in it, do 'Insert Column' -> 'Inside Group - Right', then right click the new cell and choose 'split cell' this will unmerge the 3 rows. You can then type a caption for each row. The design should now look like this.

And that's it really..

The final output looks like this.

Answer 2

Note: You can probably change the compatibility level. It should not affect anything as long as you are changing the compatibility level to a higher value and you don't have linked servers etc that are relying on this database to be at that specific version. Obviously if you are not sure, don't touch it

Anyway, there are a few ways to do this. This method use UNION, I'm not sure why you said you can't use this method, but this gives exactly what your sample expectation was like.

DECLARE @Temptable TABLE (
  Company VARCHAR(5), 
  ProcessDate DATETIME, 
  OpExp DECIMAL, 
  Tax DECIMAL, 
  Total DECIMAL);

INSERT INTO @TempTable VALUES
('Comp1', getdate(), 1000, 100, 1100),
('Comp1', dateadd(year, -1, getdate()), 2000, 200, 2200),
('Comp1', dateadd(year, -2, getdate()), 3000, 300, 3300);

SELECT * FROM @TempTable

DECLARE @ReportStartYear int = 2017

SELECT 
    t.Company
    , 'OpExp' as Val
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear -2 THEN OpExp ELSE 0 END) AS ColA
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear -1 THEN OpExp ELSE 0 END) AS ColB
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear  THEN OpExp ELSE 0 END) AS ColC
    , @ReportStartYear - 2 as ColACaption
    , @ReportStartYear - 1 as ColBCaption
    , @ReportStartYear  as ColCCaption
    FROM @Temptable t
    GROUP BY t.Company
UNION ALL
SELECT 
    t.Company
    , 'Tax' as Val
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear -2 THEN Tax ELSE 0 END) AS ColA
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear -1 THEN Tax ELSE 0 END) AS ColB
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear  THEN Tax ELSE 0 END) AS ColC
    , @ReportStartYear - 2 as ColACaption
    , @ReportStartYear - 1 as ColBCaption
    , @ReportStartYear  as ColCCaption
    FROM @Temptable t
    GROUP BY t.Company
UNION ALL
SELECT 
    t.Company
    , 'Total' as Val
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear -2 THEN Total ELSE 0 END) AS ColA
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear -1 THEN Total ELSE 0 END) AS ColB
    , SUM(CASE YEAR(t.ProcessDate) WHEN @ReportStartYear  THEN Total ELSE 0 END) AS ColC
    , @ReportStartYear - 2 as ColACaption
    , @ReportStartYear - 1 as ColBCaption
    , @ReportStartYear  as ColCCaption
    FROM @Temptable t
    GROUP BY t.Company

This returns the following

Company Val     ColA    ColB    ColC    ColACaption ColBCaption ColCCaption
Comp1   OpExp   3000    2000    1000    2015        2016        2017
Comp1   Tax     300     200     100     2015        2016        2017
Comp1   Total   3300    2200    1100    2015        2016        2017

In your report, you can set your column headers to be =FIRST(Fields!ColACaption.Value) etc so you don't have to worry about the column names being dynamic. This method works if you know how many columns you will return.

HOWEVER If might be easier to just do this in SSRS. Create a matrix, set the column group to be an expression that returns the YEAR of the ProcessDate column, then add a row within the detail group for each of your value types (OpExp, Tax & Total) and that should give you what you want also with no need to change any SQL.