Need Help On Pivoting --Pls - 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 2000 Forums

Transact-SQL (2000)

Need Help On Pivoting --Pls

Author

Topic

meg
Starting Member

4 Posts

Posted - 2006-08-25 : 03:15:34

Hi,

I need to create a report in asp.net and it includes pivoting. I've done the pivoting and i get the result. But now i nead to add another row in which it groups the columns. Not sure if i sound clear. I have a table that has a few columns like date,source,race and state. I need to generate a report. The table stores the details of callers including name,age,sex. But the report need to display in this format counting the number of calls for each state,each source and race distinctly.

source state race
star sel ind
newstraits KL malay
mlymail pahang china

I need to display the report as

date star newstraits mlymail sel Kl pahang ind malay china
10/2/2006 2 3 4 1 2 4 5 6 7

I've done that using this query

SELECT
date,
SUM(CASE WHEN source = 'star' THEN 1 ELSE 0 END) AS star,
SUM(CASE WHEN source = 'newstraits' THEN 1 ELSE 0 END) AS newstraits,
SUM(CASE WHEN source = 'mlymail' THEN 1 ELSE 0 END) AS mlymail
FROM
caller
GROUP BY
date

But now what i need to do is

group in to this and display as another header above the columns

Press
star
newstraits
mlymail

state
sel
Kl
pahang

race
ind
malay
china

Press state race
date star newstraits mlymail sel Kl pahang ind malay china
10/2/2006 2 3 4 1 2 4 5 6 7

To group the results by category. Do i need another table for category?

Pls help me,its quite urgent and i really have no idea how to create the sql statement and bind it to my asp.net datagrid.

Thank you in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 03:24:23

Maybe something like this? But why would you?

SELECT		'date' 'date',
		'star' 'star',
		'newstraits' 'newstraits',
		'mlymail' 'mlymail'
UNION ALL
SELECT		date,
		SUM(CASE WHEN source = 'star' THEN 1 ELSE 0 END) AS star,
		SUM(CASE WHEN source = 'newstraits' THEN 1 ELSE 0 END) AS newstraits,
		SUM(CASE WHEN source = 'mlymail' THEN 1 ELSE 0 END) AS mlymail
FROM		caller
GROUP BY	date

Peter Larsson
Helsingborg, Sweden

meg
Starting Member

4 Posts

Posted - 2006-08-25 : 03:43:17

Hi Peso,

will the example you suggested give the results as i wanted. Maybe i dont sound clear. i need to group the column as u see the results i have press, race and state. now above the results i need new columns as

press race sex state
date star mlymail malay chinese indian male female unknown phg sel kedah
11/08/2006 2 3 4 2 3 2 3 2 4 2 3

I hope this is clear.
Thanks peso.hope you can help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 03:48:39

If you want me to see the result you want, please report with tags [ code ] (#) button around the result.

The tag does not have spaces in it. it is just written so here to clarify.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 03:50:07

quote:
Originally posted by meg

Hi Peso,

will the example you suggested give the results as i wanted.

Yes it should. But you have to run the query yourself. Try and test.Then get back with some more information what is wrong.

Peter Larsson
Helsingborg, Sweden

meg
Starting Member

4 Posts

Posted - 2006-08-25 : 04:34:23

Hi peso,

i need the results as in the image.

[url]www.ineers.com/images/result.gif[/url]

The query i'm using now which is

SELECT 
date,
SUM(CASE WHEN source = 'star' THEN 1 ELSE 0 END) AS star,
SUM(CASE WHEN source = 'newstraits' THEN 1 ELSE 0 END) AS newstraits,
SUM(CASE WHEN source = 'mlymail' THEN 1 ELSE 0 END) AS mlymail
FROM
caller
GROUP BY 
date

gives me this results

[url]www.ineers.com/images/oldresults.gif[/url]

so i need a header that groups the column above the current selected column.

Thanks for your time.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 04:41:06

How are you going to center a category header over two columns?
This is a presention issue, not a SQL issue.

If it is ok, just put the category header over the first column in that header.

SELECT		'' '',
		'press' 'press',
		'' '',
		'state' 'state'
UNION ALL
SELECT		'date' 'date',
		'star' 'star',
		'newstraits' 'newstraits',
		'mlymail' 'mlymail'
UNION ALL
SELECT		date,
		SUM(CASE WHEN source = 'star' THEN 1 ELSE 0 END) AS star,
		SUM(CASE WHEN source = 'newstraits' THEN 1 ELSE 0 END) AS newstraits,
		SUM(CASE WHEN source = 'mlymail' THEN 1 ELSE 0 END) AS mlymail
FROM		caller
GROUP BY	date

Peter Larsson
Helsingborg, Sweden

meg
Starting Member

4 Posts

Posted - 2006-08-25 : 05:17:49

Hi,

I've modified the query and i get this error. I modified because the original query is long and just to see if this is working.

SELECT	'date' 'date',
		'press' 'press',
		'mag' 'mag',	
		'other' 'other'
UNION ALL
SELECT		'date' 'date',
		'straits time' 'straitstime',
		'today' 'today',
		'zaobao' 'zaobao'
UNION ALL
SELECT		callindate,
		SUM(CASE WHEN source = 'straits time' THEN 1 ELSE 0 END) AS straitstime,
		SUM(CASE WHEN source = 'today' THEN 1 ELSE 0 END) AS today,
		SUM(CASE WHEN source = 'zaobao' THEN 1 ELSE 0 END) AS zaobao
FROM 		caller 
where 		country='singapore'
GROUP BY callindate

If i use

SELECT		'' '',
		'press' 'press',
		'' '',
		'state' 'state'

i get this error :-

"Cannot use empty object or column names. Use a single space if necessary."

So i had to give value to all header.

now my error is :-

"Syntax error converting datetime from character string."

Can you see where have I gone wrong.

Thanks

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-25 : 13:48:49

Meg,

quote:

now my error is :-

"Syntax error converting datetime from character string."

Can you see where have I gone wrong.

Yes. <g>

From BOL:

quote:

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets combined using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types.

So, if you place a CAST() around the date field and before each SUM() call, converting to varchar, you should be fine.


SELECT	'date' 'date',
		'press' 'press',
		'mag' 'mag',	
		'other' 'other'
UNION ALL
SELECT		'date' 'date',
		'straits time' 'straitstime',
		'today' 'today',
		'zaobao' 'zaobao'
UNION ALL
SELECT		CAST(callindate as varchar),
		CAST(SUM(CASE WHEN source = 'straits time' THEN 1 ELSE 0 END) as varchar) AS straitstime,
		CAST(SUM(CASE WHEN source = 'today' THEN 1 ELSE 0 END) as varchar) AS today,
		CAST(SUM(CASE WHEN source = 'zaobao' THEN 1 ELSE 0 END) as varchar) AS zaobao
FROM 		caller 
where 		country='singapore'
GROUP BY callindate

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources