CODEWITHSUNDEEP
sqlsql-serverpivot
Vivek Raj
Vivek Raj

Reputation: 456

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

I have two tables Users and UserAttributes in SQL DB.

Table have structure like

enter image description here

Out required is

enter image description here

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

enter image description here

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.

Upvotes: 1

Views: 553

Answers (5)

Dhiraj Kumar
Dhiraj Kumar

Reputation: 48

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.

Upvotes: 0

Shival
Shival

Reputation: 274

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

Upvotes: 0

Nick
Nick

Reputation: 147206

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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;

Upvotes: 0

Related Questions