T-sql help!
-
Hi pals,
Need small help.
There is a test data with aggregated values from which i need to generated the below report.
I got stuck over here??? ( whether to use PIVOT / UNPIVOT / anything else).
Any help would be greatly appreciated.
Am also attaching the script file.
Thanks in Advance.
CREATE TABLE TEST
(
District VARCHAR(10),
School VARCHAR(10),
Gender VARCHAR(10),
Race VARCHAR(20),
[Count] INT
)
GO
DELETE from TEST
GO
insert TEST(District,School,Gender,Race,Count) values('D1','S1','M','NULL',90)
insert TEST(District,School,Gender,Race,Count) values('D1','S1','F','NULL',30)
insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','White',20)
insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','Black',40)
insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','American Indian',50)
insert TEST(District,School,Gender,Race,Count) values('D1','S1','NULL','Asian American',10)
insert TEST(District,School,Gender,Race,Count) values('D1','S2','M','NULL',100)
insert TEST(District,School,Gender,Race,Count) values('D1','S2','F','NULL',20)
insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','White',50)
insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','Black',10)
insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','American Indian',50)
insert TEST(District,School,Gender,Race,Count) values('D1','S2','NULL','Asian American',10)
GO
Report should be displayed as follows
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DISTRICT| SCHOOL| MALES| FEMALES| Total Students(males+females)| WHITES| BLACK| American Indian | Asian American | Total Non-Whites|Percent Non-White students
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D1 S1 90 3090+30 20 40 50 10 (Black+AmericanIndian+AsianAmerican) (Black+AmericanIndian+AsianAmerican)/Total Students*100
D1 S2 100 20 100+20 50 10 50 10
-
This should get you started
SELECT District,School,SUM(CASE WHEN Gender='M' THEN Count ELSE 0 END) AS MALES,
SUM(CASE WHEN Gender='F' THEN Count ELSE 0 END) AS FEMALES,
SUM(CASE WHEN Race='White' THEN Count ELSE 0 END) AS WHITES,
SUM(CASE WHEN Race='Black' THEN Count ELSE 0 END) AS BLACKS
FROM Test
GROUP BY District,School
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank You!
I will do the remaining part.
-
As a side bar... the report looks a little racist... I thought the days of Whites vs Non-Whites was gone. Guess the days of truly equal opportunity still don't exist. :Whistling:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply