Merge two rows with same value but different column values in Microsoft SQL

Transact-SQL
1

Hi,

I've a table with multiple identical value in UserId and would like a query that would merge it in a row and add columns

I created the table and query in sql fiddle

for example, i have

UserId  WorkGroup

Bill    PC-money
Bill    PC-windows
Linus   PC-free
Linus   PC-open

I would like to see

UserId  WorkGroup1 WorkGroup2
Bill    PC-money   PC-windows
Linus   PC-free    PC-open

my access to ms sql is read-only

any idea?
Thanks

2

Like this...

WITH	
	cte_AddRN AS (
		SELECT 
			mt.UserId, mt.WorkGroup,
			RN = ROW_NUMBER() OVER (PARTITION BY mt.UserId ORDER BY mt.WorkGroup)
		FROM
			#MyTable mt
		)
SELECT 
	arn.UserId, 
	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)
FROM
	cte_AddRN arn
GROUP BY 
	arn.UserId;
1 Like
3

Hi Jason,
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

WITH    
                cte_AddRN AS (
                                SELECT
                                                mt.UserId, mt.WorkGroup,
                                                RN = ROW_NUMBER() OVER (PARTITION BY mt.UserId ORDER BY mt.WorkGroup)
                                FROM
                                                CIC_PROD.dbo.UserWorkgroups mt where workgroup like '%%motclm%%'
                                )
SELECT
                arn.UserId,
                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)
FROM
                cte_AddRN arn
GROUP BY
                arn.UserId;

if i would want to join a table without using your answer, i would do this

SELECT
                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
FROM
                CIC_PROD.dbo.Individual
JOIN
                CIC_PROD.dbo.UserWorkgroups
                ON CIC_PROD.dbo.UserWorkgroups.UserId = CIC_PROD.dbo.Individual.ICUserID

How would I use JOIN in your query?

Thanks

4

a facebook friend helped me

WITH
cte_AddRN AS (
SELECT mt.UserId, mt.WorkGroup,
RN = ROW_NUMBER() OVER (PARTITION BY mt.UserId ORDER BY mt.WorkGroup)
FROM CIC_PROD.dbo.UserWorkgroups mt where workgroup like '%%motclm%%'
)
 
SELECT arn.UserId
,I.FirstName
,I.LastName
,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)
 
 
FROM cte_AddRN arn
inner join CIC_PROD.dbo.Individual I on I.ICUserID = arn.UserId
inner join CIC_PROD.dbo.UserWorkgroups w on w.UserId = arn.UserId
 
GROUP BY arn.UserId
,I.FirstName
,I.LastName
5

Sorry to butt in, but you should really consider loosing the nolock.

Much has been written about this topic, also from the experts in this forum, so please use it carefully (if ever).

1 Like
6

+1000

7

Dont be sorry! thanks for the tip. i will read more about it