Click here to Skip to main content
15,881,033 members
Please Sign up or sign in to vote.
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
Updated 21-Feb-23 0:10am
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.

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
 
Share this answer
 
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
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900