August 5, 2015 at 7:26 am
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
August 5, 2015 at 7:37 am
sharonmtowler (8/5/2015)
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
You can do this by pivoting the data. If you plan to limit the number of transfers to the 1st 20, that solves the 1 problem.
The real question is... What are you trying to accomplish?
If you're trying to generate a query for a report. You may be better off letting the reporting software do the pivot.
August 5, 2015 at 7:40 am
sharonmtowler (8/5/2015)
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
I agree with Jason's comment about letting the software do the pivot for you. This isn't always an option of course. Instead of a PIVOT I prefer to use crosstabs. Our friend Jeff Moden has a couple of awesome article on the topic. You can find them by following the links in my signature about converting rows to columns. Give it a shot and see if you can make it work. If you run into any issues feel free to post back here and we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2015 at 7:49 am
This should illustrate how to do it in SQL... (Since no one ever takes the "let the reporting software do it" advise.)
-- Test Data --
IF OBJECT_ID('tempdb..#Transfers') IS NOT NULL
DROP TABLE #Transfers;
CREATE TABLE #Transfers (
PatientID INT,
Location VARCHAR(10),
EnterTime DATETIME
);
INSERT #Transfers (PatientID,Location,EnterTime) VALUES
(1, '4D04', '2/9/2015 2:35'),
(1, '4D14', '2/9/2015 8:44'),
(1, 'RECOVERY', '2/9/2015 9:08'),
(1, '4D13', '2/9/2015 17:36'),
(1, '4D14', '2/10/2015 2:02');
-- The Solution --
WITH TransferRN AS (
SELECT
t.PatientID,
t.Location,
t.EnterTime,
row_number() OVER (PARTITION BY t.PatientID ORDER BY t.EnterTime) AS RN
FROM
#Transfers t
)
SELECT
t.PatientID,
MAX(CASE WHEN t.RN = 1 THEN t.Location END) AS Location_1,
MAX(CASE WHEN t.RN = 1 THEN t.EnterTime END) AS EnterTime_1,
MAX(CASE WHEN t.RN = 2 THEN t.Location END) AS Location_2,
MAX(CASE WHEN t.RN = 2 THEN t.EnterTime END) AS EnterTime_2,
MAX(CASE WHEN t.RN = 3 THEN t.Location END) AS Location_3,
MAX(CASE WHEN t.RN = 3 THEN t.EnterTime END) AS EnterTime_3,
MAX(CASE WHEN t.RN = 4 THEN t.Location END) AS Location_4,
MAX(CASE WHEN t.RN = 4 THEN t.EnterTime END) AS EnterTime_4,
MAX(CASE WHEN t.RN = 5 THEN t.Location END) AS Location_5,
MAX(CASE WHEN t.RN = 5 THEN t.EnterTime END) AS EnterTime_5,
MAX(CASE WHEN t.RN = 6 THEN t.Location END) AS Location_6,
MAX(CASE WHEN t.RN = 6 THEN t.EnterTime END) AS EnterTime_6
-- Follow the same pattern out to 20...
FROM
TransferRN t
GROUP BY
t.PatientID
HTH,
Jason
August 5, 2015 at 8:03 am
someone sent me this from a different forum
ROW_NUMBER() OVER (PARTITION BY S.SESSIONID ORDER BY PL.enter_time) AS location_sequence_id
works like a charm!!!
August 5, 2015 at 8:29 am
sharonmtowler (8/5/2015)
someone sent me this from a different forumROW_NUMBER() OVER (PARTITION BY S.SESSIONID ORDER BY PL.enter_time) AS location_sequence_id
works like a charm!!!
If SessionID was a necessary part of the solution, you probably should have included it in the OP...
August 5, 2015 at 8:57 am
did not know it was until i was presented with my resolution.
August 5, 2015 at 9:04 am
sharonmtowler (8/5/2015)
did not know it was until i was presented with my resolution.
The point he was making is that in your post you never said anything about sessionid. Obviously on "the other forum" you must have posted more information than here. You only mentioned two columns on this forum and neither of them were sessionid. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2015 at 10:08 am
no i did not. i posted the same information.
i was given this by someone
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY enter_time) AS
i know enough that i need to substitute the sessionid for patientid.
thanks for your help anyway.
August 5, 2015 at 10:36 am
sharonmtowler (8/5/2015)
no i did not. i posted the same information.i was given this by someone
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY enter_time) AS
i know enough that i need to substitute the sessionid for patientid.
thanks for your help anyway.
Glad you were able to get it working and thanks for letting us know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply