T-sql Report help! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-sql Report help!

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-07-20 : 09:06:34

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.

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 30 90+30 20 40 50 10 (Black+AmericanIndian+AsianAmerican) (Black+AmericanIndian+AsianAmerican)/Total Students*100
D1 S2 100 20 100+20 50 10 50 10

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 09:47:49

something like this


SELECT	District, School,
	Males	= SUM(case when Gender = 'M' then Count else 0 end),
	Females	= SUM(case when Gender = 'F' then Count else 0 end),
	Total	= SUM(case when Gender in ('M', 'F') then Count else 0 end),
	. . .
FROM	TEST t
GROUP BY District, School

KH
[spoiler]Time is always against us[/spoiler]