Solved

Like

Posted on 2008-10-13
4
142 Views
Last Modified: 2010-04-21
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
Comment
Question by:Mr_Shaw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 22701625
unfortunately, with a PIVOT you have to know the values for while you're going to pivot.
0
 

Author Comment

by:Mr_Shaw
ID: 22701645
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22701779
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
 

Author Closing Comment

by:Mr_Shaw
ID: 31406258
Thanks,
Better to know that I can't do it than try for hours in vain...
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question