Dynamic Pivot/Crosstab with string values 1

[travisbrown]

I'm working with two existing and populated tables in SS2005 and trying to pivot one of them up into a join. The first table is a master table of contest entrants. The second table is an index of keys and values related to the contest entries. So for example:

TBL_CONTESTS

123 | Joe | Blow | test@test.com | 001 | 2012/01/01
124 | Jane | Doe | test1@test.com | 001 | 2012/01/01

TBL_CONTEST_VALUES

123 | question 1 | answer one
123 | question 2 | answer two
123 | question 4 | answer four
124 | question 1 | answer one
124 | question 2 | answer two
124 | question 3 | answer three
....

I'm trying to pivot the second table up into a cross tab to display the answers in line with the master table entries, like this:

123 | Joe | Blow | test@test.com | 001 | 2012/01/01 | answer one | answer two | answer three | answer four ...

All I've been able to figure out with a dynamic crosstabs is display aggregate values where I need to display the literal values. I thought I could do this with MAX, maybe.

Here's where I've got to:

DECLARE @columns as varchar(max)

SELECT @columns =  STUFF(( SELECT DISTINCT ',[' + entry_key + ']' FROM dbo.TBL_CONTEST_VALUES FOR XML PATH('')) , 1, 1, '')

DECLARE @pivot_query as varchar(max)

SET @pivot_query = 'SELECT entry_id, entry_fname, entry_lname, entry_email, contest_id, contest_name, entry_ts, ' + @columns + ' FROM
(SELECT C.entry_fname, C.entry_lname, C.entry_email, C.contest_id, C.contest_name, C.entry_ts, CV.entry_id, CV.entry_key, CV.entry_value FROM dbo.TBL_CONTEST_VALUES CV INNER JOIN dbo.TBL_CONTESTS C ON C.entry_id = CV.entry_id  ) as S
PIVOT
(MAX(entry_key) FOR entry_value IN ('+ @columns + ')) AS P'
--PRINT @pivot_query
EXEC(@pivot_query)

Here are the table definitions:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_CONTESTS](
	[entry_id] [int] IDENTITY(1,1) NOT NULL,
	[entry_fname] [nvarchar](50) NULL,
	[entry_lname] [nvarchar](50) NULL,
	[entry_email] [nvarchar](255) NULL,
	[contest_id] [int] NULL,
	[contest_name] [nvarchar](100) NULL,
	[entry_ts] [smalldatetime] NULL CONSTRAINT [DF_TBL_CONTESTS_entry_ts]  DEFAULT (getdate()),
 CONSTRAINT [PK_TBL_CONTESTS] PRIMARY KEY CLUSTERED 
(
	[entry_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TBL_CONTEST_VALUES](
	[entry_id] [int] NULL,
	[entry_key] [nvarchar](50) NULL,
	[entry_value] [nvarchar](50) NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[TBL_CONTEST_VALUES]  WITH CHECK ADD  CONSTRAINT [FK_TBL_CONTEST_VALUES_TBL_CONTESTS] FOREIGN KEY([entry_id])
REFERENCES [dbo].[TBL_CONTESTS] ([entry_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TBL_CONTEST_VALUES] CHECK CONSTRAINT [FK_TBL_CONTEST_VALUES_TBL_CONTESTS]

 

[markros]

Your query seems to be correct. What is the output you're getting and what is the problem?

PluralSight Learning Library

 

[travisbrown]

The fact that it seems to be correct *is* the problem

With MAX I'm getting NULL for pivoted values. If I use COUNT just to test, I get 0.

 

[markros]

I see the problem, didn't notice it before. You put entry_key and entry_value in the wrong place, e.g. your query should be

DECLARE @columns as varchar(max)

SELECT @columns =  STUFF(( SELECT DISTINCT ',[' + entry_key + ']' FROM dbo.TBL_CONTEST_VALUES FOR XML PATH('')) , 1, 1, '')

DECLARE @pivot_query as varchar(max)

SET @pivot_query = 'SELECT entry_id, entry_fname, entry_lname, entry_email, contest_id, contest_name, entry_ts, ' + @columns + ' FROM
(SELECT C.entry_fname, C.entry_lname, C.entry_email, C.contest_id, C.contest_name, C.entry_ts, CV.entry_id, CV.entry_key, CV.entry_value FROM dbo.TBL_CONTEST_VALUES CV INNER JOIN dbo.TBL_CONTESTS C ON C.entry_id = CV.entry_id  ) as S
PIVOT
(MAX(entry_value) FOR entry_key IN ('+ @columns + ')) AS P'
--PRINT @pivot_query
EXEC(@pivot_query)

Assuming that entry_key is 'question1, question2' and entry_value is 'answer1, answer2'

PluralSight Learning Library

 

[travisbrown]

Looks so obvious now

Thanks. Good catch.