none
problem getting desired crosstab/pivot result using case when ...

    Question

  • Using the following test data (sql Server 2008 express) and the following tsq to pivot the data to get the count of sessions per group, but my tsql is not putting the results on the same line for each group -- as will be seen...

    create table #tmpA(fld1 varchar(10), grp int, sess char(1))

    insert into #tmpA
    select 'tom', 1, 'a' union all
    select 'tom', 2, 'b' union all
    select 'tom', 3, 'c' union all
    select 'bill', 2, 'a' union all
    select 'bill', 3, 'b' union all
    select 'sue', 1, 'c' union all
    select 'sue', 2, 'a' union all
    select 'sue', 3, 'b' union all
    select 'joe', 1, 'a' union all
    select 'joe', 2, 'b' union all
    select 'joe', 3, 'c' union all
    select 'steve', 1, 'c' union all
    select 'steve', 2, 'c' union all
    select 'steve', 3, 'a' union all
    select 'jane', 1, 'a' union all
    select 'jane', 2, 'b' union all
    select 'jane', 3, 'c'

    select grp,
    COUNT(case when sess = 'a' then sess end) as 'a',
    COUNT(case when sess = 'b' then sess end) as 'b',
    COUNT(case when sess = 'c' then sess end) as 'c'
    from #tmpA
    group by grp, sess

    --this is the result I get but not the result I want

    grp  a   b   c
    1     3   0   0
    2     2   0   0
    3     1   0   0
    2     0   3   0
    3     0   2   0
    1     0   0   2
    2     0   0   1
    3     0   0   3

     --this is the result I want

    grp  a   b   c
    1     3   0   2
    2     2   3   1
    3     1   2   3

    I want the count of sessions per group on the same line -- how to achieve this result with tSql?

    Thanks


    Rich P

    Tuesday, July 23, 2013 9:26 PM

Answers

  • select grp, 
    COUNT(case when sess = 'a' then sess end) as 'a',
    COUNT(case when sess = 'b' then sess end) as 'b',
    COUNT(case when sess = 'c' then sess end) as 'c'
    from #tmpA
    group by grp 

    Tuesday, July 23, 2013 9:35 PM
    Moderator

All replies

  • select grp, 
    COUNT(case when sess = 'a' then sess end) as 'a',
    COUNT(case when sess = 'b' then sess end) as 'b',
    COUNT(case when sess = 'c' then sess end) as 'c'
    from #tmpA
    group by grp 

    Tuesday, July 23, 2013 9:35 PM
    Moderator
  • Thanks (my brain dump :)

    Rich P

    Tuesday, July 23, 2013 10:06 PM