A Little Crosstab Query Trouble

[travisbrown]

I'm trying to figure out this cross tab for SQL2000. I think I'm on the right track, but a little stumped. Every time I do these, I seem to completely forget how I did them last time.

Three Tables

1. tbl_reg - table of all event registrations
2. tbl_reg_int - table of selected interests by registrants
3. lkup_interests - lookup table of labels for interests.

I need to return all the records in tbl_reg joined to a crosstab of all related interests

So it would look like

reg_id,<other reg fields>, interest1, interest2, interest3
123, <other reg fields> , 1 , 0 , 1

Here's the sproc I'm trying to use (taken form here:

http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables).

Below is the table setup.

CREATE PROCEDURE Pivot 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

GO 
EXECUTE Pivot 
'SELECT tbl_reg_int.reg_id FROM tbl_reg_int GROUP BY reg_id','COUNT(int_id)','int_label','dbo.lkup_interests'
GO

USE [pbwcreg]
GO
-- BEGINNING TRANSACTION STRUCTURE
PRINT 'Beginning transaction STRUCTURE'
BEGIN TRANSACTION _STRUCTURE_
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- Create Table lkup_interests
Print 'Create Table lkup_interests'
GO
CREATE TABLE [dbo].[lkup_interests] (
		[int_id]        int NOT NULL IDENTITY(1, 1),
		[int_label]     varchar(100) NULL
)
ON [PRIMARY]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING OFF
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- Create Table tbl_reg_int
Print 'Create Table tbl_reg_int'
GO
CREATE TABLE [dbo].[tbl_reg_int] (
		[int_id]     int NULL,
		[reg_id]     int NULL
)
ON [PRIMARY]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- Create Table tbl_reg
Print 'Create Table tbl_reg'
GO
CREATE TABLE [dbo].[tbl_reg] (
		[reg_id]                    int NOT NULL IDENTITY(1, 1),
		[reg_type]                  varchar(50) NULL,
		[reg_title]                 varchar(50) NULL,
		[reg_fname]                 varchar(50) NULL,
		[reg_lname]                 varchar(50) NULL,
		[reg_company]               varchar(100) NULL,
		[reg_street_address]        varchar(100) NULL,
		[reg_city]                  varchar(50) NULL,
		[reg_state]                 varchar(50) NULL,
		[reg_zip]                   varchar(25) NULL,
		[reg_email]                 varchar(100) NULL,
		[reg_phone]                 varchar(50) NULL,
		[reg_fax]                   varchar(50) NULL,
		[reg_additional_info1]      varchar(300) NULL,
		[reg_additional_info2]      varchar(300) NULL,
		[reg_additional_info3]      varchar(300) NULL,
		[reg_additional_info4]      varchar(300) NULL,
		[reg_cc_fname]              varchar(50) NULL,
		[reg_cc_lname]              varchar(50) NULL,
		[reg_cc_company]            varchar(100) NULL,
		[reg_cc_billingaddress]     varchar(100) NULL,
		[reg_cc_state]              varchar(50) NULL,
		[reg_cc_city]               varchar(50) NULL,
		[reg_cc_zip]                varchar(25) NULL,
		[reg_cc_phone]              varchar(50) NULL,
		[reg_cc_type]               varchar(50) NULL,
		[reg_cc_confirm]            varchar(50) NULL,
		[reg_tax_id]                varchar(50) NULL,
		[reg_ts]                    datetime NULL
)
ON [PRIMARY]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO
-- Add Primary Key PK_tbl_reg to tbl_reg
Print 'Add Primary Key PK_tbl_reg to tbl_reg'
GO
ALTER TABLE [dbo].[tbl_reg]
	ADD
	CONSTRAINT [PK_tbl_reg]
	PRIMARY KEY
	([reg_id])
	ON [PRIMARY]
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

-- Create Foreign Key FK_tbl_reg_int_tbl_reg on tbl_reg_int
Print 'Create Foreign Key FK_tbl_reg_int_tbl_reg on tbl_reg_int'
GO
ALTER TABLE [dbo].[tbl_reg_int]
	ADD CONSTRAINT [FK_tbl_reg_int_tbl_reg]
	FOREIGN KEY ([reg_id]) REFERENCES [dbo].[tbl_reg] ([reg_id])
	ON DELETE CASCADE
	ON UPDATE CASCADE
GO

IF @@ERROR<>0 OR @@TRANCOUNT=0 BEGIN IF @@TRANCOUNT>0 ROLLBACK SET NOEXEC ON END
GO

-- COMMITTING TRANSACTION STRUCTURE
PRINT 'Committing transaction STRUCTURE'
IF @@TRANCOUNT>0
	COMMIT TRANSACTION _STRUCTURE_
GO

SET NOEXEC OFF
GO
-- BEGINNING TRANSACTION DATA
PRINT 'Beginning transaction DATA'
BEGIN TRANSACTION _DATA_
GO
SET NOCOUNT ON
SET ANSI_PADDING ON
GO

-- Deleting from table: tbl_reg
PRINT 'Deleting from table: tbl_reg'
DELETE FROM [dbo].[tbl_reg]
-- Deleting from table: tbl_reg_int
PRINT 'Deleting from table: tbl_reg_int'
DELETE FROM [dbo].[tbl_reg_int]
-- Deleting from table: lkup_interests
PRINT 'Deleting from table: lkup_interests'
DELETE FROM [dbo].[lkup_interests]

-- Insert scripts for table: lkup_interests
PRINT 'Inserting rows into table: lkup_interests'
SET IDENTITY_INSERT [dbo].[lkup_interests] ON

INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (1, 'Embarking on Your Career')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (2, 'Career Change/Re-Entry')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (3, 'Career Advancement')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (4, 'Management Training')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (5, 'Developing/Retaining Talent')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (6, 'Executive Leadership')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (7, 'Gender Communications')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (8, 'Generational Communications')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (9, 'Diversity')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (10, 'Developing Your Business Network')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (11, 'Marketing Strategies')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (12, 'Operational Excellence')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (13, 'The Global Economy')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (14, 'Business Ethics')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (15, 'Conflict Resolution')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (16, 'Starting Your Own Business')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (17, 'Stress Management')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (18, 'Women’s Health & Fitness')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (19, 'Work/Life Balance')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (20, 'Personal Finance')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (21, 'Community Leadership/Philanthropy')
INSERT INTO [dbo].[lkup_interests] ([int_id], [int_label]) VALUES (22, 'Retirement')

SET IDENTITY_INSERT [dbo].[lkup_interests] OFF

-- Insert scripts for table: tbl_reg_int
PRINT 'Inserting rows into table: tbl_reg_int'
INSERT INTO [dbo].[tbl_reg_int] ([int_id], [reg_id]) VALUES (4, 53)
INSERT INTO [dbo].[tbl_reg_int] ([int_id], [reg_id]) VALUES (6, 53)
INSERT INTO [dbo].[tbl_reg_int] ([int_id], [reg_id]) VALUES (8, 53)
INSERT INTO [dbo].[tbl_reg_int] ([int_id], [reg_id]) VALUES (21, 53)

-- Insert scripts for table: tbl_reg
PRINT 'Inserting rows into table: tbl_reg'
SET IDENTITY_INSERT [dbo].[tbl_reg] ON

INSERT INTO [dbo].[tbl_reg] ([reg_id], [reg_type], [reg_title], [reg_fname], [reg_lname], [reg_company], [reg_street_address], [reg_city], [reg_state], [reg_zip], [reg_email], [reg_phone], [reg_fax], [reg_additional_info1], [reg_additional_info2], [reg_additional_info3], [reg_additional_info4], [reg_cc_fname], [reg_cc_lname], [reg_cc_company], [reg_cc_billingaddress], [reg_cc_state], [reg_cc_city], [reg_cc_zip], [reg_cc_phone], [reg_cc_type], [reg_cc_confirm], [reg_tax_id], [reg_ts]) VALUES (53, 'attendee', 'Development Officer', 'FNAME', 'LNAME', 'ABC', '123 Any Street', 'San Francisco', 'California', '94105', 'test@test.org', '415-123-1234', '123-456-1234', '', 'NO', '', '', 'FNAME LNAME', 'TEST', 'TEST', '111 W. Fourth Avenue #201', 'California', 'San Mateo', '94402', '123-123-1234', 'mastercard', '', '', '123456 12:58:54')
SET IDENTITY_INSERT [dbo].[tbl_reg] OFF


-- COMMITTING TRANSACTION DATA
PRINT 'Committing transaction DATA'
IF @@TRANCOUNT>0
	COMMIT TRANSACTION _DATA_
GO

SET NOEXEC OFF
GO