October 18, 2012 at 7:57 am
Hello, I have the following query that returns 2 rows and I would like to have the results in 1 row. I was wondering if this could done.
-- Query
;WITH BaseData AS (
SELECT
observ_value,
prod_id_code1,
create_timestamp,
modify_timestamp,
ROW_NUMBER() OVER (PARTITION BY person_id, obs_id ORDER BY modify_timestamp DESC) rn
FROM
dbo.lab_results_obx
WHERE
person_id = 'AB123G-QQH'
AND (obs_id like '%BLOOD GROUP%' OR obs_id like '%RH TYPE%' or obs_id like '%RHTYPE%')
AND observ_value IN('A', 'B', 'AB', 'O', 'POSITIVE', 'NEGATIVE')
)
SELECT
observ_value
FROM
BaseData
WHERE
rn = 1
-- Results
observ_value
A
Negative
I would like the results to be as follows in 2 columns on one row:
A Negative
Thank you!
October 18, 2012 at 8:21 am
Maybe you can do something like this:
;WITH BaseData AS (
SELECT
observ_value,
prod_id_code1,
create_timestamp,
modify_timestamp,
ROW_NUMBER() OVER (PARTITION BY person_id, obs_id ORDER BY modify_timestamp DESC) rn
FROM
dbo.lab_results_obx
WHERE
person_id = 'AB123G-QQH'
AND (obs_id like '%BLOOD GROUP%' OR obs_id like '%RH TYPE%' or obs_id like '%RHTYPE%')
AND observ_value IN('A', 'B', 'AB', 'O', 'POSITIVE', 'NEGATIVE')
)
SELECT
MAX( CASE WHEN observ_value IN ('A', 'B', 'AB', 'O') THEN observ_value END) AS blood_group,
MAX( CASE WHEN observ_value IN ('POSITIVE', 'NEGATIVE') THEN observ_value END) AS rh_type
FROM
BaseData
WHERE
rn = 1
That will give you 2 columns.
For a better explanation, check this article: http://www.sqlservercentral.com/articles/T-SQL/63681/
October 18, 2012 at 8:51 am
Luis, this works perfect.
Thank you,
David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply