SQL multiple lines into one
This seems like a simple task, but I can't get around it.
I just need to find out, users who have used mobile phones, users who have used the desktop and users who have used both, day in and day out. Each user / access_dt combination must contain only 1 line.
Below is the following data:
USER, ACCESS_DATE, FORMFACTOR
1 01-01-2014 Mobile
1 01-01-2014 Desktop
2 01-01-2014 Mobile
3 01-01-2014 Desktop
Desired output:
USER, ACCESS_DATE, KEY_MOBILE, KEY_DESKTOP, KEY_MOBILE_DESKTOP
1 01-01-2014 1 1 1
2 01-01-2014 1 0 0
3 01-01-2014 0 1 0
Many thanks!
+1
source to share
1 answer
This is a summary query, basically. I would do it with conditional aggregation:
select user, access_date,
max(case when FORMFACTOR = 'Mobile' then 1 else 0 end) as KEY_MOBILE,
max(case when FORMFACTOR = 'Desktop' then 1 else 0 end) as KEY_DESKTOP,
(case when max(case when FORMFACTOR = 'Mobile' then 1 else 0 end) > 0 and
max(case when FORMFACTOR = 'Desktop' then 1 else 0 end) > 0
then 1 else 0
end) as KEY_MOBILE_DESKTOP
from table t
group by user, access_date;
+4
source to share