Search code examples
sqlsql-serverunpivot

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

Solution

  • 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);