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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy