Transpose and Create Variables

Question

I have data that looks like:

PT_ID   Time        HR       RR      SysBP
   1     830        120      24    
   1     900        124               118
   1    1000                          115
   1    1045        118      20

ETC.

Desired output:

 PT_ID   Time1  HR1   RR1   SysBP1   Time2   HR2   RR2   SysBp2   Time3   Etc
   1      830    24                   900    124          118      1000

This type of manipulation is too tricky for my novice knowledge of SQL and was curious if you all know of a potential solution.

you can use Pivot example here https://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query — junketsu, Dec 10 '18 at 18:29

score 4 · Accepted Answer · edited Dec 10 '18 at 20:03

4

If you have an known, or maximum number of observations, you can use a PIVOT in concert with Row_Number() (assuming TIME is the proper sequence)

If the number of observations/columns is not known, then you'll have to use some dynamic SQL.

Example

Select *
 From  (
        Select A.[PT_ID] 
              ,B.*
         From  (Select *,RN=Row_Number() over (Partition By PT_ID Order by Time) From YourTable ) A
         Cross Apply (values (concat('Time' ,RN),[Time])
                            ,(concat('HR'   ,RN),[HR])
                            ,(concat('RR'   ,RN),[RR])
                            ,(concat('SysBP',RN),[SysBP])
                     ) B(Item,Value)
       ) src
 Pivot (sum(Value) for Item in  ([Time1],[HR1],[RR1],[SysBP1]
                                ,[Time2],[HR2],[RR2],[SysBP2]
                                ,[Time3],[HR3],[RR3],[SysBP3]
                                ,[Time4],[HR4],[RR4],[SysBP4]
                                ) ) pvt

Returns

I accidentally edited this post and did not mean to, deleting the edit.

edited Dec 10 '18 at 20:03

Raven

849
6
17

answered Dec 10 '18 at 18:36

John Cappelletti

79,615
7
44
66

Thanks @johncappelletti, I failed to mention that my time column is a little complex in that it contains both the date and time. I tried running the follow code -it removes the date (see updated post) but it still leaves me with an undesirable result, for example: "15:154:02.000000". I found potential solutions here but am having issues. https://stackoverflow.com/questions/7710449/how-to-get-time-from-datetime-format-in-sql – Raven Dec 10 '18 at 19:47
@Greg Always happy to help. – John Cappelletti Dec 10 '18 at 19:47
Hi again @John_cappelletti, if you get a minute, I was wondering if you could please comment in our code what is occurring at ",B.*"||| ")B(Item,value)"||| ) "src"||| "(sum(value)" and ")) pvt". Thanks! – Raven Dec 10 '18 at 21:09
@Greg The Row_Number() portion will essentially create the column groupings/sequence. Cross Apply B will unpivot your data. Then it is a small matter for the Pivot to finish it up. Perhaps the best thing you can do to better visualize is run the Row_Number() query first. See the results. Then you an run the query within SRC. – John Cappelletti Dec 10 '18 at 21:33
Great, thank you again! When running the code I get the follow error "Msg 8117, Level 16, State 1, Line 1 Operand data type datetime2 is invalid for sum operator." I believe this is due to my time variable column including both the date and time. Do you have any advice in how to adjust the code for my lack of detail when explaining my data to you? Sorry! – Raven Dec 10 '18 at 21:39

Transpose and Create Variables

1 Answers1