如何根据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

如何根据条件将列值分成多行。结果表将显示在网格视图中。我在多个select语句中尝试使用Count(*)但不是我所期望的。提前致谢

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

2 个解决方案

#1

Try below mentioned simple query to get Total as well.

尝试下面提到的简单查询以获得Total。

;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!

您将获得所需的输出!

#2

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

#1