Pivot Table

[Guest]

Does anyone know of a site outlining a good method of implementing a Pivot
table style grid using aGridView control or similar?

 

[michdoh]

HI

We had a requirement to display summary information in a pivot table so
created a cross-tab query and used that as the datasource.

This is based on a table containing information on MCMS templates but
the logic can be applied to any table

CREATE PROCEDURE dbo.sp_GetPostingSummaryInfoByServiceArea
(
@StartDateAsString varchar(30),
@FinishDateAsString varchar(30),
@ServiceArea varchar(10)
)

AS

IF @StartDateAsString = '' SET @StartDateAsString = '01/01/1950
00:00:00'
IF @FinishDateAsString = '' SET @FinishDateAsString = '31/12/9999
23:59:59'
IF @ServiceArea = '' SET @ServiceArea = '%'

SELECT auth_serv_area,
count(CASE posting_state WHEN 1 then posting_state end) AS Saved,
count(CASE posting_state WHEN 2 then posting_state end) AS
Ed_Approval,
count(CASE posting_state WHEN 3 then posting_state end) AS
Editor_Dec,
count(CASE posting_state WHEN 4 then posting_state end) AS Mod_App,

count(CASE posting_state WHEN 5 then posting_state end) AS Modr_Dec,

count(CASE posting_state WHEN 6 then posting_state end) AS Approved,

count(CASE posting_state WHEN 7 then posting_state end) AS Published,
count(CASE posting_state WHEN 1 then posting_state end) +
count(CASE posting_state WHEN 2 then posting_state end) +
count(CASE posting_state WHEN 3 then posting_state end) +
count(CASE posting_state WHEN 4 then posting_state end) +
count(CASE posting_state WHEN 5 then posting_state end) +
count(CASE posting_state WHEN 6 then posting_state end) +
count(CASE posting_state WHEN 7 then posting_state end) as
ServiceTotal,
count(CASE HasLiveVersion WHEN 'True' then HasLiveVersion end) as
PostWtLiveVer
FROM postings
where (@StartDateAsString <= last_modified) and
(@FinishDateAsString >= last_modified) and
(auth_serv_area like @ServiceArea)
GROUP BY auth_serv_area
order by auth_serv_area
GO

Mikey