0

I have a table which has patient information and some values. The patient identifier can be repetitive in a table i.e., there can be multiple records per patientID. Example of the table:

ID  Result
1   Positive
1   Negative
2   NULL
2   Negative
2   In-conclusive
3   Negative 
3   Positive
3   
4   Positive
2   Positive

I need the above values converted from rows to columns. It should look like below:

ID  Result1 Result2 Result3 Result4
1   Positive    Negative        
2   Negative    Inconclusive    Positive    
3   Negative    Positive        
4   Positive

Pivot uses aggregate functions to accomplish the task. But I want to use PIVOT without aggregate function. Is that possible?

Can you please tell me how to do it?

TIA, Sri

Serg
  • 22,285
  • 5
  • 21
  • 48
Srividhya
  • 1
  • 3
  • Pivot uses aggregate functions to accomplish the task. But I want to use PIVOT without aggregate function. Is that possible? – Srividhya Jun 02 '16 at 14:08
  • Yes, use `MIN()` or `MAX()` aggregated function. and `row_number()` to know what column go each value. – Juan Carlos Oropeza Jun 02 '16 at 14:10
  • Thanks for your reply. I think using row_number will be a good solution. But I can't quite get my head around how to use row_number to get value to update in columns, Result1, Result2 etc. Can you please explain how this can be done especially when the source table has tens of thousands of records? – Srividhya Jun 02 '16 at 14:29
  • do you know if the max number of result is 4 or is random? if random you may need dynamic pivot instead. http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Juan Carlos Oropeza Jun 02 '16 at 14:31
  • Well, the maximum number of 'different' results is 4. But when I consider the dataset as a whole it is random. – Srividhya Jun 02 '16 at 14:35
  • Are you ok, if I set the example with max 4 result, even if the data only has 3? – Juan Carlos Oropeza Jun 02 '16 at 14:38
  • Yes please. Thanks – Srividhya Jun 02 '16 at 14:44
  • The problem I see is you dont have a column to know what is the order of patient results? I guess ID is patientID? But you should include a date so you know if test were `Positive-Negative` or `Negative-Positive` – Juan Carlos Oropeza Jun 02 '16 at 14:47
  • There is a date column in my table and yes the ID is patinetID – Srividhya Jun 02 '16 at 14:49
  • Try this one. http://rextester.com/XQYUH4010 change the `order by` to your date column – Juan Carlos Oropeza Jun 02 '16 at 14:53
  • Wow!!! That's great. It works. Thank you very much. Just a final question. Is there a way to put the results of the query into a table? – Srividhya Jun 02 '16 at 15:14
  • Never mind. I have managed to insert the results into a new table.Thanks ever so much for your help – Srividhya Jun 02 '16 at 15:43

0 Answers0