SQL将多行合为一 [英] SQL multiple rows into one

问题描述

这似乎是一件容易的事，但我似乎无法将其包裹住.

It seems like an easy task, but i can't seem to wrap my head around it.

我只需要每天查明使用手机的用户，使用台式机的用户和使用台式机的用户.每个user/access_dt组合只能产生1行.

I just need to find out, users, that used mobiles, users that used desktop and users that used both, on day to day basis. Every user/access_dt combination should result only 1 row.

示例数据如下:

USER, ACCESS_DATE, FORMFACTOR
   1   01-01-2014      Mobile
   1   01-01-2014      Desktop
   2   01-01-2014      Mobile
   3   01-01-2014      Desktop

所需的输出:

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

非常感谢您！

推荐答案

基本上，这是一个透视查询.我可以通过条件聚合来做到这一点:

This is a pivot 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;

这篇关于SQL将多行合为一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！