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.