SQL 2000 - How to show the column records as seperate Columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NewBee - 29 Mar 2007 18:06 GMT

Hi,

I am new in the field and might be asking a dumb question, but please
I still need help.
I am running the Business Object 5.1.8, and using Freehand SQL with
Oracle 8.1 connecting.

I have four columns e;g

Patient_Name Account_No Birthdate Result Result
Value
Test One 123 1950
Pain 2
Test One 123 1950
Anxiety 5
Test TWO 234 1980 Nausea
1
TEST Three 456 1970 Anxiety
3
TEST Three 456 1970
Pain 4

I would like to have the results in the follow order:

Patient_Name Account_No Birthdate Pain Anxiety
Nausea
Test One 123 1950
2 5
Test TWO 234
1980 1
TEST Three 456 1970
4 3

Is there anybody who can help me.

Reply to this Message

Jana - 29 Mar 2007 21:01 GMT

> Hi,
>
[quoted text clipped - 30 lines]
>
> Is there anybody who can help me.

NewBee:

If you are only dealing with a few 'Result' options, and your data is
consistently entered, then you can add columns to your query like this
(Paste each of these lines into a blank column of your query grid):

Pain: IIF([Result] = "Pain",[Result Value])
Anxiety: IIF([Result] = "Anxiety",[Result Value])
Nausea: IIF([Result] = "Nausea",[Result Value])

HTH,
Jana

Reply to this Message

Jana - 29 Mar 2007 21:11 GMT

> > Hi,
>
[quoted text clipped - 45 lines]
>
> - Show quoted text -

This is, of course, assuming you're using Access. Since you specified
several other programs, I'm not sure if you intended to post to an
Access group. If you need the same functionality within an SQL
statement, it would be something like this:

SELECT Patient_Name, Account_No, Birthdate,
IF Result = 'Pain'
THEN Result_Value
ENDIF AS Pain,
IF Result = 'Anxiety'
THEN Result_Value
ENDIF AS Anxiety,
IF Result = 'Nausea'
THEN Result_Value
ENDIF AS Nausea
FROM YourTableName

Jana

Reply to this Message