thanks for that answer. it's working awsome. I'm trying to join another table to it but keep failing.
the query based on your answer in my production environment is
cte_AddRN AS (
RN = ROW_NUMBER() OVER (PARTITION BY mt.UserId ORDER BY mt.WorkGroup)
CIC_PROD.dbo.UserWorkgroups mt where workgroup like '%%motclm%%'
WorkGroup_1 = MAX(CASE WHEN arn.RN = 1 THEN arn.WorkGroup END),
WorkGroup_2 = MAX(CASE WHEN arn.RN = 2 THEN arn.WorkGroup END),
WorkGroup_3 = MAX(CASE WHEN arn.RN = 3 THEN arn.WorkGroup END),
WorkGroup_4 = MAX(CASE WHEN arn.RN = 4 THEN arn.WorkGroup END)
if i would want to join a table without using your answer, i would do this
CIC_PROD.dbo.Individual.ICUserID, CIC_PROD.dbo.Individual.FirstName, CIC_PROD.dbo.Individual.LastName, CIC_PROD.dbo.UserWorkgroups.UserId, CIC_PROD.dbo.UserWorkgroups.WorkGroup
ON CIC_PROD.dbo.UserWorkgroups.UserId = CIC_PROD.dbo.Individual.ICUserID
How would I use JOIN in your query?