• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

Like

I have a  pivot query, which is;

SELECT     AttendanceDate, [C100] AS C1, [C200] AS C2
FROM         (SELECT     AttendanceDate, Derived_Provider
                       FROM          OPA_General
                       WHERE      (AttendanceDate BETWEEN CONVERT(DATETIME, '03 /01/ 2007 00:00:00', 103) AND CONVERT(DATETIME, '05/03/2007 00:00:00', 103)))
                      p PIVOT (COUNT(Deriverd_Provider) FOR Derived_Provider IN (C100, C200)) AS pvt
ORDER BY AttendanceDate

I would like to change the IN clause to pick up values which are LIKE C100. This also means I will have to change the first select clause to pick up variations of C100. I am not sure how to do either of these tasks.
0
Mr_Shaw
Asked:
Mr_Shaw
  • 2
  • 2
1 Solution
 
chapmandewCommented:
unfortunately, with a PIVOT you have to know the values for while you're going to pivot.
0
 
Mr_ShawAuthor Commented:
I have previously tried to do the same thing with a case statements, but never achieved it. Do yoyu know how to use Case to act like a pivot
0
 
chapmandewCommented:
sure:

select --do some sort of aggregations here
from
(
select
case1 = case when fieldvalue = 1 then 'somevalue' else 0 end,
case2 = case when fieldvalue = 1 then 'somevalue' else 0 end,
case3 = case when fieldvalue = 1 then 'somevalue' else 0 end,
case4 = case when fieldvalue = 1 then 'somevalue' else 0 end
from table
) a
group by....
0
 
Mr_ShawAuthor Commented:
Thanks,
Better to know that I can't do it than try for hours in vain...
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now