June 24, 2015 at 12:12 pm
I have a query that pulled data in the following format:
First_Name Last_Name Drug Reason
Jim Smith Aspirin
Jim Smith Headache
Here's what I would like for it to display:
Jim Smith Aspirin Headache
Our fields and are weird. I had to use a case statement to get drugs and reason.
Would putting a max on them put everything on one row?
June 24, 2015 at 12:21 pm
A general pattern would be:
SELECT (group by columns), y = MAX(CASE WHEN x THEN y END), z = MAX(CASE WHEN x THEN z END)
GROUP BY (group by columns)
The aggregation function doesn't have to be MAX, SUM is also handy. You might want to get counts of multiple conditions with one table scan using multiple "SUM(CASE WHEN x THEN 1 ELSE 0 END)" expressions.
You can also use PIVOT.
June 24, 2015 at 1:16 pm
Thanks! So, are you saying something like this?
Select column A, column B, max (case when) as Drug, max (case when) as Reason
From.....
Group by column A, column B
June 25, 2015 at 1:11 pm
cory.bullard76 (6/24/2015)
Thanks! So, are you saying something like this?Select column A, column B, max (case when) as Drug, max (case when) as Reason
From.....
Group by column A, column B
The one thing you'll need is to have a certainty about which record contains the drug and which contains the reason. Order alone is inadequate, as there is no inherent order within SQL Server, without an ORDER BY clause. As the incoming data represents two different things using the same field, you have to be able to uniquely identify the input in order, meaning either the import has to happen by something like a VBScript, where you know you'll get the incoming records in their original sequence, or you need data that identifies that record as having a type of Drug or Reason. Alternatively, you could have a table with all possible drugs and all possible reasons (ensuring there's zero dupes between drugs and reasons) that you can join to that will give you the type.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply