[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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