Create columns in query for multiple values in one column

1

I want to know if it is possible to do the following;
I have patients that may have been transferred to different locations(see below)

location_name enter_time
4D04 2/9/15 2:35
4D14 2/9/15 8:44
RECOVERY 3 2/9/15 9:08
4D13 2/9/15 17:36
4D14 2/10/15 2:02

i know i can do a min max to get my first and last values. I want to label the columns something like
1st location, 2nd location, 3rd location, 4th location, discharge location.
there could be 1 location or 20.
is there a way to do this?
i can do a temporary table and then an update query to add the values to those columns.
just not sure how to get the next value and then the next etc.

thanks
sharon

2

You can do the following, where the location_sequence_id column would be the location label that you are looking for.

SELECT
	location_name,
	enter_time,
	ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY enter_time) AS location_sequence_id
FROM
	YourTable;

As for figuring out the discharge location, is there another column or some other indication that the patient has been discharged (as opposed to still being in the facility)?

3

thank you, thank you, thank you!!! exactly what I am looking for!!! you rock!!!