Can someone helpl me write this query to create a crosstab(pivot t

Hi. I am trying to write a crosstab or pivot table, but I don't think the
syntax that I'm writing is very efficient. It is using a union statement to
add an overall total, but I think this is the problem that is inefficient.
Does anyone know of a better way to write this query to cut down on time?
I'm using this type syntax on a much larger scale(searching 200k record
creating a pivot table with 100 columns and between 100-200 rows).
drop table testme
Create Table Testme ( ID int PRIMARY KEY, City NVARCHAR(255), Country
NVARCHAR(255) )
Insert Into Testme (ID, City, Country) Values (1, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (2, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (3, N'NYC', 'USA')
Insert Into Testme (ID, City, Country) Values (4, N'Chicago', 'USA')
Insert Into Testme (ID, City, Country) Values (5, N'Chicago', 'USA')
select * from testme
select
[country], [total], [nyc], [chicago]
from
(
select
[country], [myorder] = 0, count([id]) as total, count(case when [city]
like 'nyc' THEN [id] else null END) as [nyc], count(case when [city] like
'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
group by
country
union
select
[country] = 'Total', [myorder] = 1, count([id]) as total, count(case
when [city] like 'nyc' THEN [id] else null END) as [nyc], count(case when
[city] like 'Chicago' THEN [id] else null END) as [chicago]
from
testme
where
city in ('nyc', 'Chicago')
) as source order by [myorder], [country]Presumably you're going to display your results somewhere, such as in
ReportingServices, or a web page, etc... So why not add them up there instea
d
if you're worried?
Personally, I'm not too keen on the PivotTable concept of SQL. I would
rather return all the data, and then use something at the presentation layer
to turn it into a PivotTable.

Can someone helpl me write this query to create a crosstab(piv

Hi Rob,
Originally I was sending the data and using a function in VB.Net to create
the pivot table, but I'm trying to have the work done on the SQL Server
instead of transferring all that data to the ASP.Net page. I think at the
moment that it's duplicating some of the work in the query but was wondering
if someone knew of a better way to write the query.
David
"Rob Farley" wrote:

> Presumably you're going to display your results somewhere, such as in
> ReportingServices, or a web page, etc... So why not add them up there inst
ead
> if you're worried?
> Personally, I'm not too keen on the PivotTable concept of SQL. I would
> rather return all the data, and then use something at the presentation lay
er
> to turn it into a PivotTable.
>Just to be sure, could you lay out the output that you are looking for'
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
> "Rob Farley" wrote:
>|||There's no pretty way of returning a PivotTable directly from SQL. You can d
o
it through a large amount of temporary table population or creating an ugly
piece of 'dynamic' SQL. But honestly, it's much easier to do it once you've
got the data away from SQL.
If you group by all your columns and rows, so that you get only one record
back for each cell of your PivotTable, then you can easily handle that in
VB.Net. If you're worried about the amount of data passed back, you could
group by IDs instead of column/row names, and pass back separate datasets
which translate the IDs into the more human-readable form.
If you're really determined to create a stored procedure that will do it for
you, then I'm sure we can come up with something, but please, pick the
'simple' solution, which is to find a control that will display the
PivotTable for you.
Rob
"David Reynolds" wrote:

> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David