asked on

Pivot Table using SQL Server 2000 and ASP

Hi Experts,

I would ideally like to display some data in an ASP front end in a Pivot Table style Resultset.

I would like to put my Columns into the first column of a table, then the fields in the second column and the Values in the next.

Table
Red | Bucket | 22
Red | Spade | 25
Blue | Bucket | 23
Blue | Spade | 24

Displaying as

Red | Blue
Bucket 22 | 23
Spade 25 | 24

I have a variable number of fields, that change fairly frequently.
Apparently SQL Server 2000 doesn't support this type of thing. I was hoping with ASP I could maybe do it?

C

imrancs

here we go:

declare @sql varchar(8000)
set @sql = ''

select @sql = @sql + 'sum(case when color = '''+ color + ''' then num end)''' + color + ''',' + char(13)
from (select distinct color from table1) t

if @sql <> ''
set @sql = left ( @sql, len(@sql) - 2 )

select @sql = 'select description, ' + @sql + 'from table1 group by description'

exec( @sql )

Imran

imrancs

I used this script to create and populate data:

create table table1 (color varchar(100), description varchar(100), num int)

insert into table1
select 'Red', 'Bucket', 22 union all
select 'Red ', 'Spade', 25 union all
select 'Blue', 'Bucket', 23 union all
select 'Blue', 'Spade', 24

Craig Lambie

ASKER

Ok, now applying to my exact situation...if you could ...I think I am almost there:

declare @sql varchar(8000)
set @sql = ''

select @sql = @sql + 'sum(case when intGroupID = '''+ intGroupID + ''' then monValue end)''' + intGroupID + ''',' + char(13)
from (select distinct intGroupID from tblGroupDailies) t

if @sql <> ''
set @sql = left ( @sql, len(@sql) - 2 )

select @sql = 'select intDailiesFieldID, ' + @sql + 'from tblGroupDailies group by intDailiesFieldID'

exec( @sql )

-------
CREATE TABLE tblGroupDailies (
            intDailiesID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION PRIMARY KEY,
            sdtDate smalldatetime NOT NULL,
            intGroupID int NOT NULL,
            intAcNoID int NULL,
            intDailiesFieldID int NOT NULL,
            monValue money NULL,
            )

------ I am getting the error -------
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '' then monValue end)'' to a column of data type int.

I am sure I am almost there..

Craig Lambie

ASKER

FYI
INSERT INTO tblGroupDailies (sdtDate, intGroupID, intAcNoID, intDailiesFieldID, monValue)
SELECT getdate(), 1, NULL, 1, 12345 UNION ALL
SELECT getdate(), 1, NULL, 2, 13456 UNION ALL
SELECT getdate(), 1, NULL, 3, 13456 UNION ALL
SELECT getdate(), 2, NULL, 1, 21456 UNION ALL
SELECT getdate(), 2, NULL, 2, 22456 UNION ALL
SELECT getdate(), 2, NULL, 3, 32456

ASKER CERTIFIED SOLUTION

imrancs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imrancs