December 14, 2011 at 5:08 am
Hi,
I have four tables, the main table is #InitialTable_18 and the other is #ClaQuestionView. My goal is to achieve the result seen in #DesiredResult table. Currently after the code I post below I achieve #CurrentResult. With this #CurrentResult table I have to add columns to the table and then perform an update in order to transpose.
ie
Alter Table #CurrentResult
ADD (
AtNight varchar(max),
Witness varchar(max),
Fault varchar(max)
)
FOLLOWED BY:
Update #CurrentResult fwf
SET
AtNight = (CASE WHEN fwf.Question = 'Did did the incident occur at night' THEN Answer ELSE '' END)
,Witness = (CASE WHEN fwf.Question = 'Were there any witnesses' THEN Answer ELSE '' END)
,Fault = (CASE WHEN fwf.Question = 'Who is at fault' THEN Answer ELSE '' END )
Here is the sample data to give you an indication of what I want.
DROP TABLE #ClaQuestionView
CREATE TABLE #ClaQuestionView (ClaCaseID INT,Question varchar(max), Answer varchar(max),QuestionID INT,QuestionClassID INT )
INSERT INTO #ClaQuestionView VALUES (1234,'Did did the incident occur at night','Y',1,20)
INSERT INTO #ClaQuestionView VALUES (1234,'Were there any witnesses','N',2,20)
INSERT INTO #ClaQuestionView VALUES (1234,'Who is at fault','OP',3,20)
INSERT INTO #ClaQuestionView VALUES (5674,'Did did the incident occur at night','Y',1,20)
INSERT INTO #ClaQuestionView VALUES (5674,'Were there any witnesses','Y',2,20)
INSERT INTO #ClaQuestionView VALUES (5674,'Who is at fault','C',3,20)
select * from #ClaQuestionView
DROP TABLE #InitialTable_18
CREATE TABLE #InitialTable_18 (NameID int, IncidentDate date, ClaCaseID INT)
INSERT INTO #InitialTable_18 VALUES (4545,'2008-11-21',1234)
INSERT INTO #InitialTable_18 VALUES (6768,'2010-07-09',5674)
select * from #InitialTable_18
DROP TABLE #DesiredResult
CREATE TABLE #DesiredResult (NameID int, IncidentDate date, ClaCaseID INT,AtNight varchar(max), Witness varchar(max), Fault varchar(max) )
INSERT INTO #DesiredResult VALUES (4545,'2008-11-21',1234,'Y','N','OP')
INSERT INTO #DesiredResult VALUES (6768,'2010-07-09',5674,'Y','Y','C')
select * from #DesiredResult
DROP TABLE #CurrentResult
CREATE TABLE #CurrentResult (NameID int,IncidentDate date, ClaCaseID INT,Question varchar(max), Answer varchar(max),QuestionID INT,QuestionClassID INT )
INSERT INTO #CurrentResult VALUES (4545,'2008-11-21',1234,'Did did the incident occur at night','Y',1,20)
INSERT INTO #CurrentResult VALUES (4545,'2008-11-21',1234,'Were there any witnesses','N',2,20)
INSERT INTO #CurrentResult VALUES (4545,'2008-11-21',1234,'Who is at fault','OP',3,20)
INSERT INTO #CurrentResult VALUES (6768,'2010-07-09',5674,'Did did the incident occur at night','Y',1,20)
INSERT INTO #CurrentResult VALUES (6768,'2010-07-09',5674,'Were there any witnesses','Y',2,20)
INSERT INTO #CurrentResult VALUES (6768,'2010-07-09',5674,'Who is at fault','C',3,20)
select * from #CurrentResult
The actual code that I use to create the #CurrentResult result table is this:
Select it.*
,cqv.QuestionID
,cqv.question
,cqv.answer
INTO #CurrentResult
from
#InitialTable_18 it
inner join #ClaQuestionView cqv
on cqv.clacaseid = it.clacaseid
WHERE (it.ClaCaseID = cqv.ClaCaseID
AND it.QuestionClassID = cqv.QuestionClassID
AND cqv.ClaQuestionHistoryID = (select max(cqv2.ClaQuestionHistoryID)
from StagingViews.ClaQuestionView cqv2
where cqv.ClaCaseID = cqv2.ClaCaseID
AND cqv.QuestionID = cqv2.QuestionID
AND cqv.QuestionClassID = cqv2.QuestionClassID
)
and cqv.question != 'is this for sos?' )
What I'm looking for is an way to obtain the #DesiredResult table without having to add columns and then perform an update.
December 14, 2011 at 5:22 am
You've missed things in your sample data.
The code you use to get the current result is as follows (from your post, but corrected to use the temp tables you provided).
SELECT it.*, cqv.QuestionID, cqv.Question, cqv.Answer
FROM #InitialTable_18 it
INNER JOIN #ClaQuestionView cqv ON cqv.ClaCaseID = it.ClaCaseID
WHERE (it.ClaCaseID = cqv.ClaCaseID AND it.QuestionClassID = cqv.QuestionClassID
AND cqv.ClaQuestionHistoryID = (SELECT max(cqv2.ClaQuestionHistoryID)
FROM #ClaQuestionView cqv2
WHERE cqv.ClaCaseID = cqv2.ClaCaseID
AND cqv.QuestionID = cqv2.QuestionID
AND cqv.QuestionClassID = cqv2.QuestionClassID)
AND cqv.Question != 'is this for sos?')
The issue with this code is that "QuestionClassID" doesn't exist in #InitialTable_18 and "ClaQuestionHistoryID" doesn't exist in #ClaQuestionView.
Post back with corrected sample data please and we'll take a look.
December 14, 2011 at 5:48 am
Hi this is more accurate sample data.
DROP TABLE #ClaQuestionView
CREATE TABLE #ClaQuestionView (ClaCaseID INT,Question varchar(max), Answer varchar(max),QuestionID INT,QuestionClassID INT,ClaQuestionHistoryID int )
INSERT INTO #ClaQuestionView VALUES (1234,'Did did the incident occur at night','Y',1,20,801)
INSERT INTO #ClaQuestionView VALUES (1234,'Were there any witnesses',null,2,20,801)
INSERT INTO #ClaQuestionView VALUES (1234,'Who is at fault','OP',3,20,801)
INSERT INTO #ClaQuestionView VALUES (1234,'Did did the incident occur at night','Y',1,20,901)
INSERT INTO #ClaQuestionView VALUES (1234,'Were there any witnesses','N',2,20,901)
INSERT INTO #ClaQuestionView VALUES (1234,'Who is at fault','OP',3,20,901)
INSERT INTO #ClaQuestionView VALUES (5674,'Did did the incident occur at night','Y',1,20,500)
INSERT INTO #ClaQuestionView VALUES (5674,'Were there any witnesses','null',2,20,500)
INSERT INTO #ClaQuestionView VALUES (5674,'Who is at fault','C',3,20,500)
INSERT INTO #ClaQuestionView VALUES (5674,'Did did the incident occur at night','Y',1,20,722)
INSERT INTO #ClaQuestionView VALUES (5674,'Were there any witnesses','Y',2,20,722)
INSERT INTO #ClaQuestionView VALUES (5674,'Who is at fault','C',3,20,722)
select * from #ClaQuestionView
As you can see the max ClaQuestionHistoryID needs to be selected which can be seen in the query. I realize there is no question 'is this for sos?' in the sample data but I've chosen only 3 questions as a sample from over 30.
As for the QuestionClassID I have added that. In the #ClaQuestionView there are multiple QuestionClassID, I am focusing only on the case when QuestionClassID = 20 to eliminate questions that are not relevant.
DROP TABLE #InitialTable_18
CREATE TABLE #InitialTable_18 (NameID int, IncidentDate date, ClaCaseID INT, QuestionClassID int)
INSERT INTO #InitialTable_18 VALUES (4545,'2008-11-21',1234,20)
INSERT INTO #InitialTable_18 VALUES (6768,'2010-07-09',5674,20)
select * from #InitialTable_18
December 14, 2011 at 6:42 am
OK, one more question 😀
Is the Question ID always 1 for 'Did did the incident occur at night', always 2 for 'Were there any witnesses' and always 3 for 'Who is at fault' ?
(I wouldn't need to ask if your database was properly normalised btw ;-))
Assuming the answer to the above question is "yes", here is the simplest way to get what you want
SELECT --it.*, cqv.QuestionID, cqv.Question, cqv.Answer
it.NameID, it.IncidentDate, it.ClaCaseID,
MAX(CASE WHEN cqv.QuestionID = 1 THEN cqv.Answer ELSE NULL END) AS AtNight,
MAX(CASE WHEN cqv.QuestionID = 2 THEN cqv.Answer ELSE NULL END) AS Witness,
MAX(CASE WHEN cqv.QuestionID = 3 THEN cqv.Answer ELSE NULL END) AS Fault
FROM #InitialTable_18 it
INNER JOIN #ClaQuestionView cqv ON cqv.ClaCaseID = it.ClaCaseID
WHERE (it.ClaCaseID = cqv.ClaCaseID AND it.QuestionClassID = cqv.QuestionClassID
AND cqv.ClaQuestionHistoryID = (SELECT max(cqv2.ClaQuestionHistoryID)
FROM #ClaQuestionView cqv2
WHERE cqv.ClaCaseID = cqv2.ClaCaseID
AND cqv.QuestionID = cqv2.QuestionID
AND cqv.QuestionClassID = cqv2.QuestionClassID)
AND cqv.Question <> 'is this for sos?')
GROUP BY it.NameID, it.IncidentDate, it.ClaCaseID
December 14, 2011 at 6:50 am
Yes it maintains the convention that you mentioned 🙂
December 14, 2011 at 6:57 am
mic.con87 (12/14/2011)
Yes it maintains the convention that you mentioned 🙂
Good 😉
Did this script work for you?
SELECT --it.*, cqv.QuestionID, cqv.Question, cqv.Answer
it.NameID, it.IncidentDate, it.ClaCaseID,
MAX(CASE WHEN cqv.QuestionID = 1 THEN cqv.Answer ELSE NULL END) AS AtNight,
MAX(CASE WHEN cqv.QuestionID = 2 THEN cqv.Answer ELSE NULL END) AS Witness,
MAX(CASE WHEN cqv.QuestionID = 3 THEN cqv.Answer ELSE NULL END) AS Fault
FROM #InitialTable_18 it
INNER JOIN #ClaQuestionView cqv ON cqv.ClaCaseID = it.ClaCaseID
WHERE (it.ClaCaseID = cqv.ClaCaseID AND it.QuestionClassID = cqv.QuestionClassID
AND cqv.ClaQuestionHistoryID = (SELECT max(cqv2.ClaQuestionHistoryID)
FROM #ClaQuestionView cqv2
WHERE cqv.ClaCaseID = cqv2.ClaCaseID
AND cqv.QuestionID = cqv2.QuestionID
AND cqv.QuestionClassID = cqv2.QuestionClassID)
AND cqv.Question <> 'is this for sos?')
GROUP BY it.NameID, it.IncidentDate, it.ClaCaseID
December 14, 2011 at 7:38 am
I'm testing your code and I've run into a few errors with the group by: Error is Incorrect syntax near '*'. and when I double click on it, "GROUP BY it.*" is highlighted.
SELECT
it.*,
MAX(CASE WHEN cqv.QuestionID = 3 THEN cqv.Answer ELSE NULL END) AS A,
MAX(CASE WHEN cqv.QuestionID = 13 THEN cqv.Answer ELSE NULL END) AS B,
MAX(CASE WHEN cqv.QuestionID = 17 THEN cqv.Answer ELSE NULL END) AS C,
MAX(CASE WHEN cqv.QuestionID = 15 THEN cqv.Answer ELSE NULL END) AS D,
MAX(CASE WHEN cqv.QuestionID = 20 THEN cqv.Answer ELSE NULL END) AS E,
MAX(CASE WHEN cqv.QuestionID = 24 THEN cqv.Answer ELSE NULL END) AS F,
MAX(CASE WHEN cqv.QuestionID = 241 THEN cqv.Answer ELSE NULL END) AS G,
MAX(CASE WHEN cqv.QuestionID = 235 THEN cqv.Answer ELSE NULL END) AS H,
MAX(CASE WHEN cqv.QuestionID = 19 THEN cqv.Answer ELSE NULL END) AS I,
MAX(CASE WHEN cqv.QuestionID = 2 THEN cqv.Answer ELSE NULL END) AS J
INTO #InitialTable_20
FROM #InitialTable_19 it
INNER JOIN StagingViews.ClaQuestionView cqv ON cqv.ClaCaseID = it.ClaCaseID
WHERE (it.ClaCaseID = cqv.ClaCaseID AND it.QuestionClassID = cqv.QuestionClassID
AND cqv.ClaQuestionHistoryID = (SELECT max(cqv2.ClaQuestionHistoryID)
FROM StagingViews.ClaQuestionView cqv2
WHERE cqv.ClaCaseID = cqv2.ClaCaseID
AND cqv.QuestionID = cqv2.QuestionID
AND cqv.QuestionClassID = cqv2.QuestionClassID)
AND cqv.Question <> 'is this for sos?'
AND cqv.QuestionClassID IN (25,25))
GROUP BY it.*
GO
December 14, 2011 at 7:48 am
You're trying to GROUP BY *, which you can't do. You have to explicitly state each column.
December 14, 2011 at 7:53 am
I've done some initial tests on the Top 50 and selected a few columns and grouped by these....Your code seems to work perfectly!!! Thank-you so much, it's actually amazing how simple you made the code and how effective it is!!! I really appreciate your help:-D
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply