crosstab query using stored procedure

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 2000 Forums

SQL Server Development (2000)

crosstab query using stored procedure

Author

Topic

patelhasu
Starting Member

13 Posts

Posted - 2002-02-25 : 16:40:13

i have this table which has 4 fields. Id, Name, Gender, Grade.
I want to create a crosstab query using stored procedure so it would crosstab by gender

i.e.
Name Id Male Female

Grade:04 21 31

Please help me..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-25 : 16:44:43

Read this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13266

You can put the SELECT statement into a stored procedure and run it. Also search SQL Team for "cross tab" or "pivot table" and read the articles.

patelhasu
Starting Member

13 Posts

Posted - 2002-02-26 : 11:20:14

This is what i did but i get an error "The sum or average aggregate operation cannot take a char data type as an argument."

SELECT Demographic.Id,
Sum(CASE Demographic.Gender WHEN 'F' THEN Gender ELSE 0 END) Female,
Sum(CASE Demographic.Gender WHEN 'M' THEN Gender ELSE 0 END) Males,
Sum(Gender) Total
FROM Demographic
GROUP BY Demographic.Id

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-26 : 11:33:56

Use Max() or Min() instead of Sum() for non-numeric data types (char, varchar, datetime, etc.)

patelhasu
Starting Member

13 Posts

Posted - 2002-02-26 : 11:40:41

I'm so sorry to bug you so much but how does the Min() or Max() work.

Thank you so much for helping me with this.
I really appreciate it.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-26 : 11:48:18

Min will find the smallest value in a set of values, Max will find the largest value. The other aggregate functions (Sum, Avg, Var, Std) can only use numeric arguments because they are arithmetic operations. Min and Max can work on any datatype.

I'm not clear on what you're trying to accomplish with this cross tab. Are you counting, summarizing, or simply displaying rows? Can you show EXACTLY how your source table looks, with some sample data, and EXACTLY how you want to output to appear? It's hard to know if this will work without some guidance.

patelhasu
Starting Member

13 Posts

Posted - 2002-02-26 : 11:54:14

Okay i have this table called Demographic
There are field Id, Grade, Gender
i.e.
Id Grade Gender
1111 04 F
2222 05 M
3333 05 F
4444 03 M

The output should be:
Grade Male Female
04 0 1
05 1 1
03 1 0

Thank You so much for your help.

Hasu

James Tow
Starting Member

2 Posts

Posted - 2002-02-26 : 12:12:36

I think you were close on the original. Why not try:

SELECT
GRADE,
MALE = SUM(CASE WHEN GENDER = 'M' THEN 1 ELSE 0 END),
FEMALE = SUM(CASE WHEN GENDER = 'F' THEN 1 ELSE 0 END)
FROM DEMOGRAPHIC
GROUP BY GRADE

patelhasu
Starting Member

13 Posts

Posted - 2002-02-26 : 12:22:59

Thank You so much
It worked really good
Thanks

jvolpe
Starting Member

2 Posts

Posted - 2005-09-01 : 11:44:05

One other question. Let's say you want to pivot this info...

Name Email
Jason V jason.volpe@x.com
Jason V jason.volpe@google.com
Jason V jason.volpe@msn.com

How do you get the email addresses into Email1 Email2 Email3, etc columns without knowing the previous email for each column. I'm wanting to do this in a view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources