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

Question

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

Tons of examples is available on SO. See for "pivot" statement. — Maciej Los, May 04 '15 at 17:23
possible duplicate of [SQL Transpose Rows as Columns](http://stackoverflow.com/questions/2099198/sql-transpose-rows-as-columns) — Maciej Los, May 04 '15 at 17:26

Vikrant · Accepted Answer · 2015-05-04T17:54:52.960

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!

score 1 · Answer 2 · answered May 04 '15 at 17:47

Assuming you know the number of columns, and it is relatively few, I think the easiest solution is to just self join:

Select distinct Cast(coalesce(a.date, b.date, c.date) as varchar) as Date
  , isnull(a.Type, '--') as Type1 
  , isnull(b.Type, '--') as Type2
  , isnull(c.Type, '--') as Type3 
from Table a
full outer join Table b
  on a.date = b.date
full outer join Table c
  on isnull(a.date, b.date) = c.date
where isnull(a.type, 'A') = 'A' 
  and isnull(b.type, 'B') = 'B' 
  and isnull(c.type, 'C') = 'C'

union all

select 'Total'
  , count(distinct case when type = 'A' then Date end)
  , count(distinct case when type = 'B' then Date end)
  , count(distinct case when type = 'C' then Date end)
from Table

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

2 Answers2