How can I merge multiple rows with same ID into one row.

Question

0.00/5 (No votes)

See more:

I have table:

ID |A |B |C
1 NULL DEF NULL
1 ABC NULL NULL
2 XYZ NULL NULL
3 IJK NULL NULL
3 NULL LMN NULL
3 NULL NULL PQR

I want to get result as
ID |A |B |C
1 ABC DEF NULL
2 XYZ NULL NULL
3 IJK LMN PQR

What I have tried:

I have tried CTE but its not exactly giving me the same result

Posted 8-Sep-17 2:26am

Divs from Bangalore

Updated 21-Feb-23 0:10am

Add a Solution

Comments

CHill60 8-Sep-17 8:46am

"I have tried CTE" ... then post the code that you have tried so we can see what is wrong with it!

ZurdoDev 8-Sep-17 8:53am

What if 1 had values in A for both records?

Divs from Bangalore 8-Sep-17 9:02am

That situation will not come.Column A,B & C will only have one value for a single ID.Its just that they are populating in different rows.My ask is how to display it in one row.

ZurdoDev 8-Sep-17 9:11am

Then Solution 1 will work.

2 solutions

Solution 2

select E.EmpId AS usercode,
E.EmpCode AS user_name,
E.FirstName AS EmployeeName,
m.EmpCode AS Managercode,
STUFF((select ',' +M.FirstName
from [dbo].[TblEmployee] e
where e.EmpId = m.EmpId
for xml path('')),1,1,'') as mangername,r.rolename
from [dbo].[TblEmployee] e
inner join [dbo].[TblEmployee] M ON M.EmpId =E.ManagerId
inner JOIN [User].[TblRoles] r ON e.EmpId = r.RoleId

Posted 21-Feb-23 0:10am

Member 15930515

Updated 21-Feb-23 0:12am

v3

Comments

Member 15930515 21-Feb-23 6:14am

i want a usercode, username,employeename,managername and rolename by using joins
or using stuff

Member 15930515 21-Feb-23 6:15am

any one help me with this query

CHill60 21-Feb-23 6:17am

If you have a question of your own then use the red "Ask a Question" link at the top of the page. This has nothing to do with the question that was posted here.
In your question be sure to include some sample data and your expected results

Richard Deeming 21-Feb-23 8:02am

Your question is not a "solution" to someone else's question.

If you want to ask a question, then ASK A QUESTION[^]. But you're going to have to provide a lot more information than you have here if you want anyone to be able to help you.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-09-08T03:04:00

Just use GROUP BY e.g.

SQL

select ID, MAX(A) AS A, MAX(B) AS B, MAX(C) AS C from #t
GROUP BY ID

BUT! As SerenityNowDev has pointed out ... what if you have more than 1 value in A for Id = 1 ... does the MAX still give you the result you need or do you need to use another aggregate function? Only you can decide