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.
SELECT *
FROM (
SELECT DISTINCT clients.ClientID, clients.serviceID
FROM clients
) e
PIVOT (
COUNT(serviceID)
FOR serviceID in ([1],[2])
) p