Crosstabs or pivot with a varchar

Can I use a varchar is the data column of a crosstab or a pivot?

I have data in this form

UserId JobClassName
13 Janitor
13 Supervisor
22 Janitor
22 Driver
45 Janitor

What I would like to convert it to is this:

UserId JobClass1 JobClass2 JobClass3
13 Janitor Supervisor
22 Janitor Driver
45 Janitor


No I tried this

SELECT
ResumeID,
CASE WHEN JobClassName = 'Residential (House) Managerâ€*' THEN classname
ELSE '' END AS [job class 1],
CASE WHEN JobClassName = 'Residential Worker' THEN classname ELSE '' END
AS [job class 2]
FROM
(
SELECT
jt.ResumeID AS ResumeId, jc.JobClassName AS JobClassName, jc.JobClassName
AS classname
FROM
JobsDB_Resumes r,
JobsDB_JobApplicationsJobType jt
left join JobsDB_JobClass jc on jt.JobTypeID = jc.JobClassID
where
r.ResumeID = jt.ResumeID
) x

but it repeats the id.

If I put in a group then I get this :

Msg 8120, Level 16, State 1, Line 1
Column 'x.JobClassName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Thanks!

You need to add GROUP BY and then use MAX for the pivoted class columns.
Also, not a good idea to mix the old and the style JOIN syntax. Try this:

SELECT ResumeID,
MAX(CASE WHEN JobClassName = 'Residential (House) Managerâ€*'
THEN classname
END) AS [job class 1],
MAX(CASE WHEN JobClassName = 'Residential Worker'
THEN classname
END) AS [job class 2]
FROM
(
SELECT jt.ResumeID AS ResumeId,
jc.JobClassName AS JobClassName,
jc.JobClassName AS classname
FROM JobsDB_Resumes AS r
JOIN JobsDB_JobApplicationsJobType AS jt
ON r.ResumeID = jt.ResumeID
LEFT JOIN JobsDB_JobClass AS jc
ON jt.JobTypeID = jc.JobClassID
) AS x
GROUP BY ResumeId;


--
Plamen Ratchev
http://www.SQLStudio.com

Thank you so much and I really appreciate that you replied so quickly.

I never would have thought of using max, as I thought it was for numerics.
Could you explain how this works in this case?

Thanks again!

"Plamen Ratchev" wrote:

> You need to add GROUP BY and then use MAX for the pivoted class columns.
> Also, not a good idea to mix the old and the style JOIN syntax. Try this:
>
> SELECT ResumeID,
> MAX(CASE WHEN JobClassName = 'Residential (House) Managerâ€*'
> THEN classname
> END) AS [job class 1],
> MAX(CASE WHEN JobClassName = 'Residential Worker'
> THEN classname
> END) AS [job class 2]
> FROM
> (
> SELECT jt.ResumeID AS ResumeId,
> jc.JobClassName AS JobClassName,
> jc.JobClassName AS classname
> FROM JobsDB_Resumes AS r
> JOIN JobsDB_JobApplicationsJobType AS jt
> ON r.ResumeID = jt.ResumeID
> LEFT JOIN JobsDB_JobClass AS jc
> ON jt.JobTypeID = jc.JobClassID
> ) AS x
> GROUP BY ResumeId;
>
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>

This type of pivoting uses two techniques:

- Filters the columns using the CASE function. Note that when the column
value does not match it becomes NULL (because there is no ELSE in CASE).

- Applying the MAX aggregate function (MIN will work too) will collapse
the result set back to one row per grouping column (because NULLs are
ignored by MAX)


--
Plamen Ratchev
http://www.SQLStudio.com

Paul
with cte

as

(

select *,row_number() over (partition by UserId

order by JobClassName)as rnk from #t

) select userid,

max(case when rnk=1 then JobClassName end) cl1,

max(case when rnk=2 then JobClassName end) cl1,

max(case when rnk=3 then JobClassName end) cl1

from cte

group by userid

"Paul Speranza" wrote in message
news:FE454AE9-E5A3-4065-AD58-15B4136D8003-at-microsoft.com...
> Can I use a varchar is the data column of a crosstab or a pivot?
>
> I have data in this form
>
> UserId JobClassName
> 13 Janitor
> 13 Supervisor
> 22 Janitor
> 22 Driver
> 45 Janitor
>
> What I would like to convert it to is this:
>
> UserId JobClass1 JobClass2 JobClass3
> 13 Janitor Supervisor
> 22 Janitor Driver
> 45 Janitor
>
>
> No I tried this
>
> SELECT
> ResumeID,
> CASE WHEN JobClassName = 'Residential (House) Manager†' THEN classname
> ELSE '' END AS [job class 1],
> CASE WHEN JobClassName = 'Residential Worker' THEN classname ELSE '' END
> AS [job class 2]
> FROM
> (
> SELECT
> jt.ResumeID AS ResumeId, jc.JobClassName AS JobClassName, jc.JobClassName
> AS classname
> FROM
> JobsDB_Resumes r,
> JobsDB_JobApplicationsJobType jt
> left join JobsDB_JobClass jc on jt.JobTypeID = jc.JobClassID
> where
> r.ResumeID = jt.ResumeID
> ) x
>
> but it repeats the id.
>
> If I put in a group then I get this :
>
> Msg 8120, Level 16, State 1, Line 1
> Column 'x.JobClassName' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
>
> Thanks!
>