In the future, my suggestion is that you try to help your helpers a bit by providing DDL and consumable sample data like this:
DECLARE @Imbibing TABLE
(ClientID INT, DOV DATE, Form VARCHAR(10), QuestionID VARCHAR(20), Response INT)
INSERT INTO @Imbibing
SELECT 7, '3/1/2012','Audit','Alcohol1-001',0
UNION ALL SELECT 8, '8/9/2011','Audit','Alcohol1-001',0
UNION ALL SELECT 8, '1/11/2012','Audit','Alcohol1-001',0
UNION ALL SELECT 9, '8/24/2011','Audit','Alcohol1-002',0
UNION ALL SELECT 9, '2/18/2012','Audit','Alcohol1-002',1
Remember that we're all volunteers here so this kindly helps us to respond quicker, better and with tested results.
Now onto a proposed solution:
SELECT ClientID, DOV, Form
,[Alcohol1-001]=CASE QuestionID WHEN 'Alcohol1-001' THEN 1 ELSE 0 END
,[Alcohol1-002]=CASE QuestionID WHEN 'Alcohol1-002' THEN 1 ELSE 0 END
FROM @Imbibing
Mind you this is only a guess, because in your "desired results" you show 5 column headers but only 4 data columns. And one of the Alcohol1-002 results rows doesn't match what I'm thinking you probably want.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St