August 19, 2010 at 12:44 pm
I have a single table with with a primary key (System_ID), Rx number (Rx_Nbr), Drug Number (NDC_Nbr), Repackaged_Nbr, and Quantity (Quantity) fields (there are a few more fields, but not important).
I am trying to combine all the records for an RX in to a single record. An Rx can have up to 4 NDC/Quantity records.
ie.
My simple query of
SELECT System_ID, Rx_Nbr, COALESCE (NDC_Nbr, '') + COALESCE (Repackaged_Nbr, '') AS NewNDC_Nbr, Quantity
FROM dbo.vwDose
WHERE (Rx_Nbr = 171280)
returns
System_ID Rx_Nbr NewNDC_Nbr Quantity
1913266 50 10 1.000
1913267 50 12 1.000
1913268 50 151.000
1913269 50 201.000
Whereas I would like to see
System_ID - Rx - NDC1 - Qty1 - NDC2 - Qty2 - NDC3 - Qty3 - NDC4 - Qty4
1913266 - 50 - 10 - 1.000 - 12 - 1.000 - 15 - 1.000 - 20 - 1.000
If there are no records (and most will not) with data in the last 6 columns, it should be null.
I have created the query
SELECT a.Rx_Nbr, COALESCE (a.NDC_Nbr, ' ') + COALESCE (a.Repackaged_Nbr, ' ') AS NDC1, a.Quantity AS Qty1, COALESCE (b.NDC_Nbr, ' ') + COALESCE (b.Repackaged_Nbr,
' ') AS NDC2, b.Quantity AS Qty2, COALESCE (c.NDC_Nbr, ' ') + COALESCE (c.Repackaged_Nbr, ' ') AS NDC3, c.Quantity AS Qty3, COALESCE (d.NDC_Nbr, '')
+ COALESCE (d.Repackaged_Nbr, '') AS NDC4, COALESCE (d.Quantity, '') AS Qty4
FROM dbo.vwDose AS a INNER JOIN
dbo.vwDose AS b ON a.Rx_Nbr = b.Rx_Nbr AND a.System_ID < b.System_ID INNER JOIN
dbo.vwDose AS c ON a.Rx_Nbr = c.Rx_Nbr AND a.System_ID < b.System_ID AND c.System_ID > b.System_ID INNER JOIN
dbo.vwDose AS d ON a.Rx_Nbr = d.Rx_Nbr AND a.System_ID < b.System_ID AND b.System_ID < c.System_ID AND d.System_ID > c.System_ID
but it only works if there are all the fields.
I hope this is clear, and that somebody can assist me.
Thank you very much,
Frank
August 19, 2010 at 2:49 pm
Use ROW_NUMBER() OVER(PARTITION BY Rx_Nbr ORDER BY System_ID) as pos in a CTE (or subquery)
and select the data using
SELECT
Rx_Nbr,
MAX(CASE WHEN pos=1 THEN NewNDC_Nbr ELSE NULL END) AS NDC1,
MAX(CASE WHEN pos=1 THEN Quantity ELSE NULL END) AS Qty1,
MAX(CASE WHEN pos=2 THEN NewNDC_Nbr ELSE NULL END) AS NDC2,
MAX(CASE WHEN pos=2 THEN Quantity ELSE NULL END) AS Qty2
FROM cte
GROUP BY Rx_Nbr
August 19, 2010 at 3:40 pm
Thank you. Went and figured out what a cte was and how to used it, and now the query works perfectly. Thank you.
August 19, 2010 at 3:57 pm
You're welcome!
That's exactly the attitude that's needed: get an idea/code snippet, do some research, learn how it works and modify it to your needs. Excellent job on your side!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply