SELECTing DISTINCT values in CASE pivot?

  • Below is a test scenario I've created. I know it looks a little funny (e.g. why the TestingCase_Stud table?), but it creates the situation I'm trying to understand.

    --create first table

    CREATE TABLE TestingCase_Stud

    (

    Stud_IDCHAR(10)NOT NULL,

    )

    INSERT INTO TestingCase_Stud

    (

    Stud_ID

    )

    VALUES

    ('159951'),

    ('789654'),

    ('789123'),

    ('123456')

    ;

    --create second table

    CREATE TABLE TestingCase_Act

    (

    Stud_IDCHAR(10)NOT NULL,

    ActivityVARCHAR(25)NOT NULL

    )

    INSERT INTO TestingCase_Act

    (

    Stud_ID,

    Activity

    )

    VALUES

    ('123456', 'TC_Fee_1'),

    ('123456', 'TC_Fee_2'),

    ('123456', 'TC_Fee_1'),

    ('123456', 'TC_Fee_1'),

    ('789123', 'TC_Fee_2'),

    ('789654', 'TC_Fee_1')

    ;

    --Test the tables

    SELECT *

    FROM TestingCase_Stud JOIN

    TestingCase_Act ON TestingCase_Stud.Stud_ID = TestingCase_Act.Stud_ID

    --Pivot using CASE

    SELECT[TC Fee 1] = SUM(CASE WHEN Activity='TC_Fee_1' THEN 1 ELSE 0 END)

    ,[TC Fee 2] = SUM(CASE WHEN Activity='TC_Fee_2' THEN 1 ELSE 0 END)

    FROMTestingCase_Stud JOIN

    TestingCase_Act ON TestingCase_Stud.Stud_ID = TestingCase_Act.Stud_ID

    I need to display a crosstab showing there are two TC_Fee_1's and two TC_Fee_2's. In other words, I need to count distinct Stud_ID's per TC_Fee. Just to present the question from a different angle - I need the results of this in a CASE statement that displays the results across the top:

    SELECT COUNT(DISTINCT TestingCase_Stud.Stud_ID) AS Fees, Activity

    FROMTestingCase_Stud JOIN

    TestingCase_Act ON TestingCase_Stud.Stud_ID = TestingCase_Act.Stud_ID

    GROUP BY Activity

  • Try:

    with CTE as

    (

    select distinct Stud_ID, Activity from @TestingCase_Act

    )

    SELECT[TC Fee 1] = SUM(CASE WHEN c.Activity='TC_Fee_1' THEN 1 ELSE 0 END)

    ,[TC Fee 2] = SUM(CASE WHEN c.Activity='TC_Fee_2' THEN 1 ELSE 0 END)

    FROMTestingCase_Stud as s

    JOINCTE as c ON s.Stud_ID = c.Stud_ID

    Hope this helps.

  • Thanks, Imex. I think that will do it.

  • This could be another way of doing it:

    Select Activity, COUNT(Activity) As Fees

    From

    (Select *, ROW_NUMBER() Over (Partition By Stud_Id, Activity Order By Stud_Id) As rn From TestingCase_Act) As a

    Where rn = 1

    Group By Activity

    Edit: Sorry didn't see the end part of your post. By the way, why do you need the case when you can

    get the results using a much simpler query???.....It might hamper performance with a bigger

    dataset.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • Hi Vinu - Thanks for your reply. What is the simpler query? To put make the cte a subquery in the from clause? That is the only thing I could think of. But I'm open to any suggestions you have.

  • DataAnalyst110 (7/3/2012)


    Hi Vinu - Thanks for your reply. What is the simpler query? To put make the cte a subquery in the from clause? That is the only thing I could think of. But I'm open to any suggestions you have.

    I am really sorry, Mr. Analyst.

    I got this post all wrong. Was not thinking straight when I posted, I guess. Too much work.....:doze:

    Imex's solution is good. I misunderstood your post. But, if want the Columns Dynamically then you should do a search on Dynamic CrossTabs on Google.

    Please excuse me for all the confusion.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply