How convert multiple results rows to single row SQL with respective values

Question

I have two tables Users and UserAttributes in SQL DB.

Table have structure like

Out required is

My attempted query

Select * from (
Select u.UserId,u.Username,u.UserEmail,ua.objectName,ua.objectValue 
from Users u join userAttribute on u.UserId=ua.UserId
where ua.objectName='city' or pv.objectName='phone')) results
PIVOT (count(UserId)
For objectName IN ([city],[phone])) As pivot_table;

Current output

Still it is an double entry also for the reason I am not aware the city and phone results are in 0 and 1 (boolean).

Please help getting the desired output.

There is no phone available for userId : 1, so how can you desire the phone to come in output. — Shubham Nagota, Jan 28 '20 at 11:41
`from User` will never work. `USER` is a reserved keyword in SQL Server. Are you actually using T-SQL? Images of data, also, really don't help us help you. This looks like you have denormalised data though. `city` and `phone` should be 2 separate columns in your data, not 1. Or is this process so that you can fix your design? (I hope so). — Thom A, Jan 28 '20 at 11:41
@ShubhamNagota and Larnu sorry was in hurry I have edited it. Larnu this approach allows to add any objName attributes in future not just city and phone. — Vivek Raj, Jan 28 '20 at 11:52
If you have any number of attributes, the only way to pivot is to use dynamic SQL. Take a look at this question: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Nick, Jan 28 '20 at 12:01

score 1 · Answer 1 · answered Jan 28 '20 at 11:42

1

You can do aggregation :

select u.UserId, u.username, u.usermemail,
       max(case when ua.objectname = 'city' then objvalue end),
       max(case when ua.objectname = 'phone' then objvalue end)
from User u join 
     userAttribute ua
     on u.UserId = ua.UserId
group by u.UserId, u.username, u.usermemail;

answered Jan 28 '20 at 11:42

Yogesh Sharma

49,870
5
26
52

Thanks @yogesh your solution is also correct but as Gordon answered first I have accepted it. – Vivek Raj Jan 28 '20 at 12:32

score 1 · Answer 2 · answered Jan 28 '20 at 11:42

You can use conditional aggregation to extract the values from the UserAttributes table:

SELECT u.userid, u.userName, u.userEmail,
       MAX(CASE WHEN ua.objName = 'city'  THEN ua.objValue END) AS city,
       MAX(CASE WHEN ua.objName = 'phone' THEN ua.objValue END) AS phone,
FROM Users u
JOIN UserAttributes ua ON ua.userid = u.userid
GROUP BY u.userid, u.userName, u.userEmail

score 0 · Accepted Answer · answered Jan 28 '20 at 11:40

To pivot just two columns, joins is convenient:

select u.*, uac.objvalue as city, uap.objvalue as phone
from users u left join
     userattributes uac
     on uac.userid = u.userid and
        uac.objname = 'city' left join
     userattributes uap
     on uap.userid = u.userid and
        uap.objname = 'phone';

If you want only rows with matches, then add:

where uac.objvalue is not null or uap.objvalue is not null

If you have more columns, then join and group by is probably a better approach:

select u.*, au.*
from users u join
     (select ua.userid,
             max(case when objname = 'city' then objvalue end) as city,
             max(case when objname = 'phone' then objvalue end) as phone
      from userattributes ua
      group by ua.userid
     ) ua
     on u.userid = ua.userid;

Thanks @Gordan as you said I have used "join and group by" approach to get required results. — Vivek Raj, Jan 28 '20 at 12:30

score 0 · Answer 4 · answered Jan 28 '20 at 12:19

Please try following query. Hope this will help

DECLARE @Users table(userid int, username varchar(50))
insert into @Users  select 1,'Shiv'
insert into @Users  select 2,'Ajay'

DECLARE @UserAttr table(userid int,objname varchar(100),objvalue varchar(100))
insert into @UserAttr select 1,'City','Chd'
insert into @UserAttr select 1,'phone','9646XXXX'
insert into @UserAttr select 2,'phone','8985XXXX'


select * FROM
(
select u.userid,u.username,ua.objname,ua.objvalue FROM @Users u
join  @UserAttr ua on u.userid = ua.userid
)a pivot (min(a.objvalue) FOR a.objname in ([city],[phone]))p

not able to get the required result with this query. – Vivek Raj Jan 28 '20 at 12:35 — Vivek Raj, Jan 28 '20 at 12:35

score 0 · Answer 5 · answered Jan 28 '20 at 13:00

Below is the query which you can try with or without pivot.

1. Without Pivot:- 

SELECT  ur.UserId,ur.UserName,ur.UserEmail,
        Max(CASE WHEN ua.objName = 'city' THEN ua.ObjValue END) as city,
       Max (CASE WHEN ua.ObjName = 'phone' THEN ua.ObjValue END) as Phone
FROM    Users ur 
        JOIN UserAttribute ua ON ur.UserId = ua.UserId

GROUP BY ur.UserId,ur.UserName,ur.UserEmail

2. With Pivot :- 

select * from (select us.UserId,us.UserName,us.UserEmail,ua.ObjName,ua.ObjValue from users us inner join UserAttribute ua on us.UserId=ua.UserId)t pivot
(
max(ObjValue)
For ObjName in ([city],[phone]) 

) as pivot_table;


Please try this query and let me know the results.

How convert multiple results rows to single row SQL with respective values

5 Answers5