[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

TSQL Query logic question (need help thinking something out)


Hi, Experts!

I have a query that I am writting for a report and it is making my head spin. I was hoping that maybe one of you had some ideas about what I could do. To better illustrate my question I will try to show you what the report I'm making looks like.


Grade                                  Race                        Race                           Race                            Race                        Total
xxxxx                            Male  |  Female          Male  |  Female          Male  |  Female            Male  |  Female              xxxxx
(Grade Level)              (count) | (count)         (count) | (count)          (count) | (count)           (count) | (count)             (record total)
(Grade Level)              (count) | (count)         (count) | (count)          (count) | (count)           (count) | (count)             (record total)
(Grade Level)              (count) | (count)         (count) | (count)          (count) | (count)           (count) | (count)             (record total)
(Grade Level)              (count) | (count)         (count) | (count)          (count) | (count)           (count) | (count)             (record total)
(Grade Level)              (count) | (count)         (count) | (count)          (count) | (count)           (count) | (count)             (record total)
Grand Total                 (total)  | (total)           (total)  | (total)           (total)  | (total)              (total)  | (total)               (total)  | (total)

What this report is is a count on students in particular ethnic groups. I have the query that retrieves all the data needed. My problem is that the query is grouped on grade level, then Race, then Gender. All of these items appear on the same record. For the life of me, I can't think of a way to make this report dynamic such that I don't have to hard code the Race field. I can only hope that made sense.

I'm using SQL Reporting Services 2000 by the way for this report. Any questions you might have could lead me to a solution. Of course, if you have solution that'd be nice too. Thanks!

Sincerely,

jbaisden
0
jbaisden
Asked:
jbaisden
1 Solution
 
LowfatspreadCommented:
you need to transform the data by pivoting the table...

search EE for the SP_transform or Pivot stored procedure ...

sorry gtgo
0
 
jbaisdenAuthor Commented:
Um...well that's certainly a lead. I'll look into it. Thanks!
0
 
jbaisdenAuthor Commented:

Oh...I figured adding what my current query's output looks like might help.

(this is sample data. Values listed are not actual values)

Grade                   Gender         Race                   EthnicCount
Kingergarten            M              Caucasian                 3
Kingergarten            F              Caucasian                  2
Kingergarten            M             African American        1
Kingergarten            F             African American         2

So...you see my dilema? I need Race to become a column for each race (and I also need this for Gender too). My issue is that this is first sorted by Grade, then Race, and finally Gender. I need Grade to stay on the left side so to speak and list like a regular group by clause would have it list.

I am still researching the Pivot table idea. This is a completely new term for me.

Thanks for your help, experts!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jbaisdenAuthor Commented:
To further the above exampe, here is the output I am looking for:


Grade                              Caucasian   Male    Female        African American       Male          Female
Kindergarten                                        3            2                                              1                 2


Please don't hesistate to ask any questions you might have. I would be more than happy to clarify. Okay. Off to research.
0
 
imran_fastCommented:
SOMETHING LIKE THIS
==============

select
      GRADE_LEVEL,
            SUM(CASE WHEN SEX ='MALE' AND RACE ='RACE1' THEN 1 ELSE 0 END ) RACE1_MALE,
            SUM(CASE WHEN SEX ='FEMALE' AND RACE ='RACE1' THEN 1 ELSE 0 END ) RACE1_FEMALE,
            SUM(CASE WHEN SEX ='MALE' AND RACE ='RACE2' THEN 1 ELSE 0 END ) RACE2_MALE,
            SUM(CASE WHEN SEX ='FEMALE' AND RACE ='RACE2' THEN 1 ELSE 0 END ) RACE2_FEMALE,
            .
            .
            SUM(CASE WHEN SEX ='MALE' AND RACE ='RACEN' THEN 1 ELSE 0 END ) RACEN_MALE,
            SUM(CASE WHEN SEX ='FEMALE' AND RACE ='RACEN' THEN 1 ELSE 0 END ) RACEN_FEMALE,
            SUM(CASE WHEN SEX IN ('MALE','FEMALE') AND RACE IN('RACE1',...'RACEN') THEN 1 ELSE 0 END ) TOTAL,
FROM GRADE
INNER JOIN YOURMAINTABLE
GROUP BY GRADE_LEVEL
0
 
Scott PletcherSenior DBACommented:
>> For the life of me, I can't think of a way to make this report dynamic such that I don't have to hard code the Race field. <<

You would have to use dynamic SQL, which is less desirable, mostly for security reasons.

But really, a race value shouldn't be added, changed or deleted that often anyway.  Just make them static and change the report if/when needed.
0
 
jbaisdenAuthor Commented:
That's true. I guess I'll bite the bullet and see what becomes of it.
0
 
nmcdermaidCommented:
>>  SQL Reporting Services 2000

I don't know a lot about SQL Reporting Service s but I know every other enteprprise reporting solution has a cross tab function built in. If Reporting Services has this then there is no need to pivot the data in a query - just tell reporting services to render it as a crosstab.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now