Solved

Like

Posted on 2008-10-13
4
138 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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