Crosstab conversion to SQL Server

SFAxess

unread,

Aug 26, 2003, 12:44:21 PM8/26/03

to

Hello,
I am currently trying to find the syntax for writing a
crosstab query in SQL Server to be used in a chart in my
adp. I am using data from up to three related tables, and
I have found no sample code which uses a view as the data
source.
Does anyone have some sample code which will create a
crosstab style sproc in SQL Server based on data from 3
related tables?
Any help would be much appreciated.

Alick [MSFT]

unread,

Aug 27, 2003, 4:15:37 AM8/27/03

to

Hi Sfaxess,

I find a sample script to create the crosstab output by using Sql server;
please run it in Query Analyzer.

--**** This script will show you how to create cross tab style output ***
--**** Grouping( ) function is used to determine if the data is a summary
(1).
--**** case ... when ... then ... end structure is used to programmatically
display row.
--**** convert() function is used because we wants to show character
information such as "All products".
-- So any numeric data i needed to be converted to character for column
consistency.

use northwind
select case
when(grouping(productid) = 1) and (grouping(orderid) = 1) then "All
products"
when (grouping(productid) = 1) and (grouping(orderid) <> 1) then "All
products
on order ->"
else convert(char(3),productid)
end as productid,
case
when (grouping(productid) <> 1) and (grouping(orderid) = 1)then
"All
orders of product #" + convert(char(3), productid)
when (grouping(productid) = 1) and (grouping(orderid) = 1) then
"All
orders "
else convert(char(5),orderid)
end as orderid,
sum(quantity) as total_quantity_sold
from [order details]
group by productid, orderid
with cube -- replace cube with rollup will generate different result. Try
to
see if the result is the one you are looking for.
order by productid ASC, orderid ASC -- Replace ASC with DESC will show the
reverse order of the output.

If we wan to prove that we get the correct result, run query below and
check with the result of our cross tab query.
-- Select sum(quantity) as total
-- from [order details]
-- where orderid = 11077

Please let me know if it helps.

Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| Content-Class: urn:content-classes:message
| From: "SFAxess" <noe...@aol.com>
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver

Groucho

unread,

Aug 27, 2003, 2:19:20 PM8/27/03

to

You can use the RAC utility to easily
generate all your server crosstabs.
It is similar to Access crosstab but offers
many more features/options.

www.rac4sql.net

SFAxess

unread,

Aug 29, 2003, 6:56:15 PM8/29/03

to

Thanks for responding Alick, your code has been of great
assistance.
Thanks again

> when (grouping(productid) <> 1) and (grouping
(orderid) = 1)then
>"All

>.
>

SFAxess

unread,

Aug 29, 2003, 6:56:53 PM8/29/03

to

Thanks Groucho, I'll check it out!

>.

Alick [MSFT]

unread,

Aug 31, 2003, 11:58:07 PM8/31/03

to

Glad it helps :- )

Sincerely,

Alick Ye, MCSD
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| Content-Class: urn:content-classes:message
| From: "SFAxess" <noe...@aol.com>

| NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
| X-Tomcat-NG: microsoft.public.access.adp.sqlserver