SELECTing DISTINCT values in CASE pivot?

• DataAnalyst110

  Ten Centuries

  Points: 1361

  More actions

  June 29, 2012 at 1:26 pm

  #263097

  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

• imex

  Ten Centuries

  Points: 1234

  More actions

  June 29, 2012 at 5:54 pm

  #1507387

  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.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• DataAnalyst110

  Ten Centuries

  Points: 1361

  More actions

  July 2, 2012 at 7:30 am

  #1507730

  Thanks, Imex. I think that will do it.

• vinu512

  SSCoach

  Points: 15729

  More actions

  July 2, 2012 at 11:59 pm

  #1508021

  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]

• DataAnalyst110

  Ten Centuries

  Points: 1361

  More actions

  July 3, 2012 at 7:20 am

  #1508225

  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.

• vinu512

  SSCoach

  Points: 15729

  More actions

  July 3, 2012 at 9:42 pm

  #1508560

  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]