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
– Shubham Nagota, Commented Jan 28, 2020 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
– Thom A ♦, Commented Jan 28, 2020 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
– Vivek Raj, Commented Jan 28, 2020 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: stackoverflow.com/questions/10404348/… — Nick
– Nick, Commented Jan 28, 2020 at 12:01

Yogesh Sharma · Accepted Answer · 2020-01-28 11:42:00Z

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, 2020 at 11:42

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vivek Raj Over a year ago

Thanks @yogesh your solution is also correct but as Gordon answered first I have accepted it.

Nick · Accepted Answer · 2020-01-28 11:42:49Z

1

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

answered Jan 28, 2020 at 11:42

Nick

147k23 gold badges67 silver badges106 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2020-01-28 11:40:37Z

0

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;

answered Jan 28, 2020 at 11:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Vivek Raj Over a year ago

Thanks @Gordan as you said I have used "join and group by" approach to get required results.

Shival · Accepted Answer · 2020-01-28 12:19:55Z

0

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

answered Jan 28, 2020 at 12:19

Shival

2741 gold badge2 silver badges10 bronze badges

1 Comment

Vivek Raj Over a year ago

not able to get the required result with this query.

Dhiraj Kumar · Accepted Answer · 2020-01-28 13:00:02Z

0

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.

answered Jan 28, 2020 at 13:00

Dhiraj Kumar

483 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

1 Comment

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related