April 30, 2009 at 11:32 am
I have the following query that I am trying to run against our database:
SELECT CS_Survey.Survey_Name AS Type, Config.Serial_Number, Config.Date_Purchased, Config.Date_Expiration AS Warranty_Until,
Config.Tag_Number, Contact.First_Name, Contact.Last_Name, Contact.Department_Name, CS_Survey_Detail.Question,
CS_Result_Detail.Answer
FROM CS_Survey INNER JOIN Config ON CS_Survey.CS_Survey_RecID = Config.CS_Survey_RecID
INNER JOIN CS_Survey_Detail ON CS_Survey.CS_Survey_RecID = CS_Survey_Detail.CS_Survey_RecID
INNER JOIN CS_Result_Detail ON CS_Survey_Detail.CS_Survey_Detail_RecID = CS_Result_Detail.CS_Survey_Detail_RecID
INNER JOIN Company ON Config.Company_RecID = Company.Company_RecID
INNER JOIN Contact ON Config.Contact_RecID = Contact.Contact_RecID
WHERE
(Config.CS_Survey_Recid = '24' OR Config.CS_Survey_Recid = '25' OR Config.CS_Survey_Recid = '49')
AND CS_Survey_Detail.Inactive_Flag = '0'
AND Company.Company_Name LIKE '%companyname%'
AND CS_Result_Detail.Config_RecId = Config.Config_RecID
AND (CS_Survey_Detail.Question = 'Cost' OR CS_Survey_Detail.Question = 'Expected Life Cycle (Months)')
ORDER BY Config.Config_RecID
As you can see in the WHERE clause, I'm just looking for entries that have either "Cost" or "Expected Life Cycle (Months)" in the 'Question' column. Would I be able to take those 2 values and make them columns? So I would have a 'Cost' column that then lists the appropriate values and a 'Life_Cycle' that has the number of months in it? These values are already present in the information, but in the 'Answer' column.
Sample data for those 2 columns:
Question Answer
--------- ---------
Expected Life Cycle (Months) 60
Cost 500
And I would like to see:
Expected Life Cycle (Months) Cost
---------------------------- -------
60 500
Is this doable within the SQL, or would I have to do the manipulation within my reports?
April 30, 2009 at 12:17 pm
I was able to figure this out myself - I had to use the CASE statements in the SELECT clause.
SELECT CS_Survey.Survey_Name AS Type, Config.Serial_Number, Config.Date_Purchased, Config.Date_Expiration AS Warranty_Until,
Config.Tag_Number, Contact.First_Name, Contact.Last_Name, Contact.Department_Name,
SUM(CASE WHEN CS_Survey_Detail.Question = 'Cost' THEN CAST(CS_Result_Detail.Answer AS float ) END) AS Cost,
SUM(CASE WHEN CS_Survey_Detail.Question = 'Expected Life Cycle (Months)' THEN CAST(CS_Result_Detail.Answer AS int ) END) AS Life_Cycle
FROM CS_Survey INNER JOIN Config ON CS_Survey.CS_Survey_RecID = Config.CS_Survey_RecID
INNER JOIN CS_Survey_Detail ON CS_Survey.CS_Survey_RecID = CS_Survey_Detail.CS_Survey_RecID
INNER JOIN CS_Result_Detail ON CS_Survey_Detail.CS_Survey_Detail_RecID = CS_Result_Detail.CS_Survey_Detail_RecID
INNER JOIN Company ON Config.Company_RecID = Company.Company_RecID INNER JOIN Contact ON Config.Contact_RecID = Contact.Contact_RecID
WHERE
(Config.CS_Survey_Recid = '24' OR Config.CS_Survey_Recid = '25' OR Config.CS_Survey_Recid = '49')
AND CS_Survey_Detail.Inactive_Flag = '0'
AND Company.Company_Name LIKE '%maplet%'
AND CS_Result_Detail.Config_RecId = Config.Config_RecID
AND (CS_Survey_Detail.Question = 'Cost' OR CS_Survey_Detail.Question = 'Expected Life Cycle (Months)')
GROUP BY Config.Tag_Number, Config.Serial_Number, Contact.First_Name, Contact.Last_Name, Contact.Department_Name, Config.Date_Purchased, Config.Date_Expiration,
CS_Survey.Survey_Name
ORDER BY Config.Tag_Number
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply