SQL Server Is there a way to use JOIN function on the same table

6za6bjd0 于 2023-02-28 发布在其他

关注(0)|答案(2)|浏览(175)

Number_of_Trips TIMEPART    member_casual
46477             00               casual
30162             00               casual
18686             01               member
11112             01               casual
7619              05               member
12436             05               member

I have a table where TIMEPART is same for both member_casual, however whenever I am trying to write a query to get a result where the member and casual number of trips can come on separate column correponding to their TIME PART.

I am trying to get a result like this

TIMEPART  members   Casuals
00         46477     30162                      
01         18686     11112                      
05         7619      12436

sql-server

来源：https://stackoverflow.com/questions/75532259/is-there-a-way-to-use-join-function-on-the-same-table

2条答案

按热度按时间

os8fio9y1#

If I understand you correctly you may get this result if you use PIVOT

SELECT
    * 
FROM 
    your_table PIVOT( 
        MAX(number_of_trips) FOR member_casual IN (
            'casual' AS Casuals
            , 'member' AS Members
        )
);

赞(0）回复(0）举报 2023-02-28

prdp8dxp2#

You can group by timepart and use sum with a case when filter for the two categories:

select   timepart, 
         sum(CASE member_casual WHEN 'casual' THEN number_of_trips END) as casuals, 
         sum(CASE member_casual WHEN 'member' THEN number_of_trips END) as members 
from     trips
group by timepart;

Note that your input example and desired output do not match -- probably because of some typos.

赞(0）回复(0）举报 2023-02-28

我来回答

SQL Server Is there a way to use JOIN function on the same table

2条答案

相关问题

热门标签

最新问答