June 29, 2012 at 1:26 pm
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
June 29, 2012 at 5:54 pm
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.
July 2, 2012 at 7:30 am
Thanks, Imex. I think that will do it.
July 2, 2012 at 11:59 pm
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.
July 3, 2012 at 7:20 am
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.
July 3, 2012 at 9:42 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply