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.
