Conversion failed when performing pivot on unknown number of columns

Question

Im trying to dynamically list each session a delegate books onto at an event however the number of sessions varies by event. As a result Ive been looking at solutions that do this such as this (PIVOT in sql 2005). I want to avoid using LEFT JOINs manually!

Using the code from the above example (see below) I get a "Conversion failed" error. What have I done wrong here?

Thanks for any pointers! (apologies for misformatting, my first more involved query!)

Code to create tables/data:

CREATE TABLE delegate(
   DELEGATE_REF      INTEGER  NOT NULL PRIMARY KEY 
  ,code              INTEGER  NOT NULL
  ,name              VARCHAR(10) NOT NULL
  ,MEMBER_REF        INTEGER  NOT NULL
  ,TOTAL_AMOUNT      INTEGER  NOT NULL
  ,DELEGATE_SESS_REF INTEGER  NOT NULL
  ,EVENT_REF         INTEGER  NOT NULL
);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26174,51,'Delegate A',1077419,280,58136,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26183,52,'Delegate B',1110544,302,58157,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26206,53,'Delegate C',1084626,169,58209,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26210,54,'Delegate D',1092456,257,58218,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26212,55,'Delegate E',1055867,221,58223,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26220,56,'Delegate F',1109833,169,58240,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26229,57,'Delegate G',266050,0,58258,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26230,58,'Delegate H',1110868,0,58260,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26231,59,'Delegate I',1110890,0,58262,378);
INSERT INTO delegate(DELEGATE_REF,code,name,MEMBER_REF,TOTAL_AMOUNT,DELEGATE_SESS_REF,EVENT_REF) VALUES (26232,60,'Delegate J',1110891,0,58264,378);

CREATE TABLE event(
   code      VARCHAR(6) NOT NULL
  ,event_ref INTEGER  NOT NULL PRIMARY KEY 
  ,name      VARCHAR(12) NOT NULL
);
INSERT INTO event(code,event_ref,name) VALUES ('AC2017',378,'MyConference');


CREATE TABLE delegate_session(
   DELEGATE_REF      INTEGER  NOT NULL
  ,DELEGATE_SESS_REF INTEGER  NOT NULL PRIMARY KEY 
  ,SESSION_REF       INTEGER  NOT NULL
  ,NO_DELEGATES      INTEGER  NOT NULL
);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26183,58157,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26206,58209,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26212,58223,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26220,58240,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26229,58258,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26230,58260,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26231,58262,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26232,58264,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26174,58136,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26210,58218,460,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26230,58300,461,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26231,58301,461,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26232,58302,461,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26174,58303,461,1);
INSERT INTO delegate_session(DELEGATE_REF,DELEGATE_SESS_REF,SESSION_REF,NO_DELEGATES) VALUES (26210,58304,461,1);

CREATE TABLE session(
   SESSION_REF INTEGER  NOT NULL PRIMARY KEY 
  ,NAME        VARCHAR(16) NOT NULL
);
INSERT INTO session(SESSION_REF,NAME) VALUES (460,'Delegate booking');
INSERT INTO session(SESSION_REF,NAME) VALUES (461,'Dinner booking');

This is the code I've modified to try to get the dynamic output:

DECLARE @sql AS varchar(max)
DECLARE @pivot_list AS varchar(max) -- Leave NULL for COALESCE technique
DECLARE @select_list AS varchar(max) -- Leave NULL for COALESCE technique

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + PIVOT_CODE
        , @select_list = COALESCE(@select_list + ', ', '') + 'ISNULL(' + PIVOT_CODE + ', 0) AS ' + PIVOT_CODE
FROM (
    SELECT DISTINCT s.SESSION_REF AS PIVOT_CODE
FROM DELEGATE as d
INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
where ds.NO_DELEGATES=1 and d.EVENT_REF=378
) AS PIVOT_CODES

SET @sql = '
;WITH p AS (

select d.DELEGATE_REF, s.SESSION_REF AS PIVOT_CODE, d.name, ds.NO_DELEGATES
FROM DELEGATE as d
INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
where ds.NO_DELEGATES=1 and s.EVENT_REF=378
)
SELECT DELEGATE_REF, NO_DELEGATES, ' + @select_list + '
FROM p
PIVOT (
    SUM(NO_DELEGATES)
    FOR PIVOT_CODE IN (
        ' + @pivot_list + '
    )
) AS pvt
'
EXEC (@sql)

This is the query that I am currently using to manually pull in the session with a sub-query (to show what I am looking to achieve):

select e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT, x1.t1 as 'Session 1', x2.t1 as 'Session 2'

from DELEGATE as d
INNER JOIN EVENT as e on d.EVENT_REF=e.EVENT_REF

left join (select d.DELEGATE_REF, s.name as 't1'
FROM DELEGATE as d
INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
where s.SESSION_REF=460
) as x1 on d.DELEGATE_REF=x1.delegate_ref

left join (select d.DELEGATE_REF, s.name as 't1'
FROM DELEGATE as d
INNER JOIN DELEGATE_SESSION as ds on d.DELEGATE_REF=ds.DELEGATE_REF
INNER JOIN SESSION as s on ds.SESSION_REF=s.SESSION_REF
where s.SESSION_REF=461
) as x2 on d.DELEGATE_REF=x2.delegate_ref

where d.code > 50 and d.code < 61 and e.code like 'ac2017'

group by e.NAME, d.code, d.name, d.MEMBER_REF, d.TOTAL_AMOUNT, x1.t1, x2.t1

@Cade Roux Ive tried to follow the several great examples you posted but think Im making a fundamental mistake somewhere!! — Miksmith, Feb 07 '18 at 10:23

Conversion failed when performing pivot on unknown number of columns

0 Answers0