SQL 透视 MIN( COUNT (

发布于 2024-08-05 22:34:50 字数 1077 浏览 10 评论 0原文

我有一个复杂的 MS SQL 2005 查询，其中有一个 PIVOT...

我有这样的数据：

Data ( clients left join visits ):
ClientID VisitID ServiceID
1        34      5
1        36      2
1        36      5
1        45      2
1        48      2
1        60      2
2        44      1
3        48      2
3        78      3
3        79      2

我需要的是这样的：

ID [1] [2] [3] [4] [5]
1   0  1   0   0   1
2   1  0   0   0   0
3   0  1   1   0   0

我正在使用的 SQL 查询是这样的：

select * from 
         (select clients.ClientID, clients.serviceID FROM clients left join visits on visit.cliendid=clients.clientid ) e 
         pivot ( COUNT(serviceID) for serviceID in ([1],[2],...,[54]) ) p

但是这个 SQL 查询不做我想要的，相反，它这样做：

ID [1] [2] [3] [4] [5]
1   0  4   0   0   2
2   1  0   0   0   0
3   0  2   1   0   0

我需要列中的所有数据均为 0 或 1，例如，0 从未使用过此服务，1 已使用过此服务... 我该怎么做？

如果我可以为...做数据透视 ( Math.MIN ( COUNT(serviceID), 0 ) ) 或者如果我可以为 ... 做 ( CASE (COUNT(serviceID) > 0 ) THEN 1 ELSE 0 ... 但它不会让我。

原文

I have a complicated MS SQL 2005 query with a PIVOT in it...

I have data like this :

Data ( clients left join visits ):
ClientID VisitID ServiceID
1        34      5
1        36      2
1        36      5
1        45      2
1        48      2
1        60      2
2        44      1
3        48      2
3        78      3
3        79      2

And what I need is this:

ID [1] [2] [3] [4] [5]
1   0  1   0   0   1
2   1  0   0   0   0
3   0  1   1   0   0

The SQL Query I am using is this:

select * from 
         (select clients.ClientID, clients.serviceID FROM clients left join visits on visit.cliendid=clients.clientid ) e 
         pivot ( COUNT(serviceID) for serviceID in ([1],[2],...,[54]) ) p

But this SQL Query doesn't do what I want, instead it does this:

ID [1] [2] [3] [4] [5]
1   0  4   0   0   2
2   1  0   0   0   0
3   0  2   1   0   0

I need all of the data in the columns to be either 0 or 1, as in, 0 has never used this service, 1 has used this service...
How do I do this?

It would be awesome if I could do pivot ( Math.MIN ( COUNT(serviceID), 0 ) for ... or if I could do ( CASE (COUNT(serviceID) > 0 ) THEN 1 ELSE 0 for ...
but it won't let me.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

郁金香雨 2024-08-12 22:34:50

SELECT  *
FROM    (
        SELECT  DISTINCT clients.ClientID, clients.serviceID
        FROM    clients
        ) e 
PIVOT   (
        COUNT(serviceID)
        FOR serviceID in ([1],[2])
        ) p

SELECT  *
FROM    (
        SELECT  DISTINCT clients.ClientID, clients.serviceID
        FROM    clients
        ) e 
PIVOT   (
        COUNT(serviceID)
        FOR serviceID in ([1],[2])
        ) p

回复收藏 0 原文

~没有更多了~