data parsing quandry

[MSBrady]

Oy!
SQL Server 2008 R2
EPIC Clarity 2010

I have a table with flowsheet IDs called Clarity.dbo.IP_FLWSHT_MEAS. The associated value names are in another table Clarity.dbo.IP_FLO_GP_DATA liked via FLO_MEAS_ID. The value names I'm concerned with are vitals like 'HEIGHT', 'WEIGHT', 'PULSE', 'TEMP', 'RESP', 'CVP', and 'BP'.
The project I'm on calls for the BP to be split out into BP_SYSTOLIC and BP_DIASTOLIC. So the data are like so:

FLO_DIS_NAME MEAS_VALUE
HEIGHT 235
WEIGHT 165
PULSE 100
PULSE 90
PULSE 95
BP 120/80
BP 130/90
RESP 89

But they need to be like so:
FLO_DIS_NAME HEIGHT WEIGHT PULSE BP_SYSTOLIC BP_DIASTOLIC RESP
MEAS_VALUE 235
MEAS_VALUE 165
MEAS_VALUE 100
MEAS_VALUE 90
MEAS_VALUE 95
MEAS_VALUE 120 80
MEAS_VALUE 130 90
MEAS_VALUE 89

It feels like I need a PIVOT, but I don't know how to set it up with the split that needs to happen with BP. Here is what I'm using to parse them into their own fields, but again I don't know how to incorporate this into a PIVOT so the data will display like I need them to.

SUBSTRING(flomeas.MEAS_VALUE, 1, ISNULL(NULLIF(CHARINDEX('/', flomeas.MEAS_VALUE) -1, -1), 0) ) as 'BP_SYSTOLIC'

ISNULL(SUBSTRING(flomeas.MEAS_VALUE, NULLIF(CHARINDEX('/', flomeas.MEAS_VALUE) +1, 1), 3), '') as 'BP_DIASTOLIC'

Any help is appreciated.

 

[Qik3Coder]

Do you have anything to group these into, such as a time recorded?

You can flatten these drastically, with the BP Over/Under being the easy part.

Lodlaiden

You've got questions and source code. We want both!

 

[MSBrady]

Yes RECORDED_TIME is available. Also PAT_ID.

 

[markros]

select 'MEAS_VALUE' AS FLO_DIS_NAME,

case when FLO_DIS_NAME ='HEIGHT' then Meas_Value end as Height,
case WHEN FLO_DIS_NAME =  'WEIGHT' then Meas_Value end,
--
case when FLO_DIS_NAME = 'BP' then LEFT(Meas_Value, charindex('/', Meas_Value + '/')-1) END as  BP_SYSTOLIC, 
case when FLO_DIS_NAME = 'BP' then SUBSTRING(Meas_Value, charindex('/', Meas_Value + '/')+1, LEN(Meas_Value)) END as  
   BP_DIASTOLIC, etc.
from LabResults

PluralSight Learning Library

 

[MSBrady]

Okay. Great. Thanks for the help. I was thinking a pivot was the solution.