Vikram Vikram - 6 months ago 8
SQL Question

How do I Separate One Column values into Multiple rows based on condition in SQL

How to Seperate column values into multiple rows based on Condition. Resultant table will show in

Grid view
. I tried with
Count(*)
in multiple
select
statements but not what i expected. Thanks in advance

Table: RegistrationReport

Date Type
-----------------------------------------
02/05/2015 A
04/05/2015 B
04/05/2015 C
05/05/2015 A


I need output like this:

Date Type 1 Type 2 Type 3
--------------------------------------------------
02/05/2015 A - -
04/05/2015 - B -
04/05/2015 - - C
05/05/2015 A - -
--------------------------------------------------
Total: 2 1 1

Answer

Try below mentioned simple query to get Total as well.

;with CTE as(
    SELECT Date
        ,case when Type = 'A' then 'A' else '-' end as 'Type_1'
        , case when Type = 'B' then 'B' else '-' end as 'Type_2'
        , case when Type = 'C' then 'C' else '-' end as 'Type_3'
    FROM RegistrationReport
)

select cast(Date as varchar(20))Date
    ,Type_1
    ,Type_2
    ,Type_3
from CTE
UNION ALL
SELECT 'Total:'
    ,CAST(SUM(case when Type_1= 'A' then 1 else 0 end)as varchar(10))
    ,CAST(SUM(case when Type_2= 'B' then 1 else 0 end)as varchar(10))
    ,CAST(SUM(case when Type_3= 'C' then 1 else 0 end) as varchar(10))
FROM CTE

You will get required output!