Splitting Fields out of one row into Multiple Rows

[Elvaar]

Not sure if this is possible to do with SQL or not...any help is appreciated!

Let's say I have a table that has 5 fields.
Patient_ID
Patient_Name
Pat_Height
Pat_Weight
Pat_WaistCirc

A select query on this table displays results as:

Patient_ID, Patient_Name, Pat_Height, Pat_Weight, Pat_WaistCirc

What I need is it to display like this:

Patient_ID,Patient_Name, ValueField, DataLabel
Patient_ID, Patient_Name, Pat_Height, "Height"
Patient_ID, Patient_Name, Pat_Weight, "Weight"
Patient_ID, Patient_Name, Pat_WaistCirc, "WaistCirc"

So one row of data becomes 3 rows of data, the like data repeats itself, unlike data forces a new row for each data element, and that data label is kept with the data value.


I hope someone can help with this !

Thanks in Advance!!

 

[wiremonkey22]

Not sure if this is possible to do with SQL or not...any help is appreciated!

What I need is it to display like this:

Patient_ID,Patient_Name, ValueField, DataLabel
Patient_ID, Patient_Name, Pat_Height, "Height"
Patient_ID, Patient_Name, Pat_Weight, "Weight"
Patient_ID, Patient_Name, Pat_WaistCirc, "WaistCirc"

So one row of data becomes 3 rows of data, the like data repeats itself, unlike data forces a new row for each data element, and that data label is kept with the data value.

Thanks in Advance!!

Im assuming you want some thing like this:
01 John 72"
01 John 160lb
01 John 32"

02 Bill etc....

or

01 John 72" I used dots because the post messed up the spacing
...........160lb
...........32"

02 Bill etc......

Not sure if this can be accomplished with a query i rarly use SQL but ive done similar things with Code. looping through recordsets and adding to a table then using a report however this can be a messy process and should be avoided.

If it can be done with SQL your in the right place someone will know. Good Luck

 

[Elvaar]

wiremonkey,

That is exactly what I am looking for. I am trying to avoid using code for this one.

 

[wiremonkey22]

wiremonkey,

That is exactly what I am looking for. I am trying to avoid using code for this one.

First or Second Example:

 

[stopher]

You can do it like this:

SELECT Patient_ID, Patient_Name, "Pat_Height" AS ValueField, Pat_Height AS DataLabel
FROM tblPatients
UNION
SELECT Patient_ID, Patient_Name, "Pat_Weight" AS ValueField, Pat_Weight AS DataLabel
FROM tblPatients
UNION 
SELECT Patient_ID, Patient_Name, "Pat_WaistCirc" AS ValueField, Pat_WaistCirc AS DataLabel
FROM tblPatients

Note that is is not normalised though. You need to split the result into two tables, e.g.

tblPatients
Patient_ID (PK)
Patient_Name

tblPatientStats
Patient_ID (PK)
ValueField (PK)
DataLabel

hth
Chris

 

[Elvaar]

Perfect, thanks !

 

[boblarson]

And one more suggestion -

A report can do this without the Union query, so if you made a report instead of a query, you can do this easily enough as well.

 

[giovaanni]

Another way to do this that is very easy is just create yourself another table called expand with one field called "expandnumber". In this table give this field three values, 1,2,3. Create a query with both this table and your other data table in it. Don't link the tables. This will create a cross product query, which will in essence triple all of your records from your patient data table.

Then in the query, add your Patient_ID, Patient_Name, a third calculated field that has this in it: IIF(expand.expandnumber = 1,tblpatients.pat_height,IIF(expand.expandnumber=2,tblpatients.patweight,tblpatients.pat_waistcirc)), and a fourth calculated field that has this in it: IIF(expand.expandnumber = 1,"Height",IIF(expand.expandnumber=2,"Weight","waist circ"))

Done!