Transform and reposition rows to column in SSRS

Question

I have SQL query output in a grouped table and count for each group. This means output is repeated for a grouped column. I need to transform that into one row for each group and multiple columns for each value as shown below.

I know it can be done in SSRS using groups on grouped column but stuck further more. I need a hint or starter to get it done. I tried parent grouping on first grouped column but getting format like below.

(Note: for those who unable to view attached images) FROM:

Sector | Status | Count |

Industrial | Expired | 50 |

Industrial | Valid | 10 |

Industrial | New | 45 |

Energy | Expired | 30 |

Energy | Valid | 90 |

Energy | New | 45 |

I need to transform/reformat above SQL query output to something like following

---

Sector      | Expired | Valid | New | Total |

Industrial |    50      |   10   |   45   |  105  |

Energy     |    30      |   90   |    46  |   166 |

Vs Current progress.

Answer 1

This is really simple using a Matrix control on your report.

Add a matrix, then just drag the Sector to the row placeholder, status to the column placeholder and count to the data placeholder. This will give you the basic report.

To add the total column, right click the column group (that was automat8ically created when you dragged the Status field to the column placeholder) then choose Add Total - After.

Here's the basic report done in 20 seconds.. (right-click and open in new windows to make it bigger if required)