Post reply

Pivot Query

  • October 18, 2012 at 7:57 am

    #268765

    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

    #1550524

    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://qa.sqlservercentral.com/articles/T-SQL/63681/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 18, 2012 at 8:51 am

    #1550538

    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