RustyHamster RustyHamster - 2 months ago 6
SQL Question

Columns to rows

I am working with SQL 2008 database
Im working on a report i want to display in a graph for me to do this i need to convert my results from columns into rows
Here is my query

SELECT
COUNT(CASE WHEN (pm.mortgagestatus) = 10 then 1 end) AS Received,
SUM(case WHEN (pm.mortgagestatus) = 10 then pm.AmountRequested Else 0 END) AS [Received Amount],
COUNT(CASE WHEN (pm.mortgagestatus) = 4 then 1 end) AS Passed,
SUM(case WHEN (pm.mortgagestatus) = 4 then pm.AmountRequested Else 0 END) AS [Passed Amount],
COUNT(CASE WHEN (pm.mortgagestatus) = 5 then 1 end) AS packaged,
SUM(case WHEN (pm.mortgagestatus) = 5 then pm.AmountRequested Else 0 END) AS [Packaged Amount],
COUNT(CASE WHEN (pm.mortgagestatus) = 6 then 1 end) AS Offered,
SUM(case WHEN (pm.mortgagestatus) = 6 then pm.AmountRequested Else 0 END) AS [Offered Amount]
FROM tbl_Profile AS p
INNER JOIN tbl_Profile_Mortgage AS pm
ON p.id = pm.fk_ProfileID


This Query returns
enter image description here

I would like the query to return like this.

enter image description here

This will allow me to put the data into a chart.
Any help with this is most appreciated.
Cheers

Answer

Use aggregation with a case:

SELECT (CASE WHEN pm.mortgagestatus = 10  then 'Received'
             WHEN pm.mortgagestatus = 4  then 'Passed'
             WHEN pm.mortgagestatu) = 5  then 'packaged'
             WHEN pm.mortgagestatus = 6  then 'Offered'
        END) as Mortgage_Status,
       COUNT(*) as Total,
       SUM(pm.AmountRequested) as LoanAmount
FROM tbl_Profile p INNER JOIN
     tbl_Profile_Mortgage pm
     ON p.id = pm.fk_ProfileID
WHERE pm.mortgagestatus IN (4, 5, 6, 10)
GROUP BY (CASE WHEN pm.mortgagestatus = 10  then 'Received'
               WHEN pm.mortgagestatus = 4  then 'Passed'
               WHEN pm.mortgagestatu) = 5  then 'packaged'
               WHEN pm.mortgagestatus = 6  then 'Offered'
          END);