1

I wasn't sure what a proper title would be, I hope this works.

I'm having trouble converting rows to columns with a certain table, and found possible solutions online. However, I feel like I need to edit this table to enable me to actually convert the rows into columns.

The table looks a little bit like this:

ID1 patientID patientname study_date echo_var

 1   1234      Bob         01-01-2014 15
 2   1234                  01-01-2000 13
 3   1234      Bob         08-08-2012 14
 4   1234      Bob         13-02-2005 13
 5   6625      Louie       12-11-2010 25
 6   6625                  08-10-1998 20
 7   6625      Louie       19-04-2006 21

This is information automatically harvested from an electronic patient system and normally contains a lot more variables per row, but I'm only interested in one (echo-var) right now. All the rows represent specific patient visits. Because of changes in the electronic system the tool I use can't properly extract all data from older records, which is why some of the names aren't filled in properly. Seeing as their patientID is unique for every patient I can fill the names in manually, but there's about 6000 records so I was hoping there would be a more elegant manner of doing so.

This is not much of a bother though, the major thing is converting the rows into columns (see below). So I found a solution online for my problem, but I feel like I am hindered because the separate datapoints per patient aren't numbered uniquely for every patient, as below. I want every seperate datapoint to be numbered for each patient, so the first visit, chronologically, is numbered 1 for each patient individually, and the second is 2, etc.

 ID1 patientID patientname study_date echo_var echo_number

 1   1234      Bob         01-01-2014 15        4
 2   1234      Bob         01-01-2000 13        1
 3   1234      Bob         08-08-2012 14        3
 4   1234      Bob         13-02-2005 13        2
 5   6625      Louie       12-11-2010 25        3
 6   6625      Louie       08-10-1998 20        1
 7   6625      Louie       19-04-2006 21        2

If I have this I think I know how to convert the rows to columns, which is what I ultimately want, like below.

patientID echo_number1 study_date1 echo_var1 echo_number2 study_date2 echo_var2
1234      1            01-01-2000  13        2            13-02-2005  13
6625      1            08-10-1998  20        2            19-04-2006  21

There would be rows for date no. 3 and its respective measurement and so forth. I guess the echo_number(x) variables aren't crucial, but the dates and respective measurement would be.

Does anyone know the proper way to go for this? As I've mentioned, I've tried searching for these particular problems but I'm having trouble finding a solution. If anything's unclear please let me know. If anyone knows how how to go from the first table to the last (without the steps in between), that would work as well.

Many thanks in advance

Using a table similar to the second one above, I've tried the following queries:

SELECT 
    [patientID],
    "echo_number" & [echo_number] & "_" & "echo_var" AS [ValueID],
    [echo_var] AS [ValueValue]
FROM [TABLENAME]
UNION ALL SELECT 
    [patientID],
    "echo_number" & [echo_number] & "_" & "study_date" AS [ValueID],
    [study_date] AS [ValueValue]
FROM [TABLENAME];

Followed by:

TRANSFORM Sum(NEWQUERY.ValueValue) AS SumOfValueValue
SELECT NEWQUERY.patientID
FROM NEWQUERY
GROUP BY NEWQUERY.patientID
PIVOT NEWQUERY.ValueID
RoyH
  • 11
  • 2
  • You're going to need that `echo_number` column - probably best to set it in a preprocessing step. – Andre Apr 20 '18 at 09:52
  • @Andre thank you, that link was very useful. I managed to add row numbers like I wanted, however I'm having trouble pivoting the table because whenever I use the study_date variable (when in the second step of the link you provided) I get an error message saying "Data type mismatch in criteria expression". I deleted all blank values and tried messing with the data format (text/date/etc) but that didn't matter. Most answers online seem to revolve around the latter issue, but I'm not sure that's the case here. Does anyone have any ideas? – RoyH Apr 20 '18 at 12:16
  • Please add your current SQL attempt that creates the "Data type mismatch" error to your question. – Andre Apr 20 '18 at 12:50
  • @Andre, I added my attempts in my original post. The first query works fine, but after the second one I get the error message. When replacing the data variable with another random number variable similar to echo_var, it works fine and I get two rows for each visit, but using the data variable (even when changing the format) doesn't work. – RoyH Apr 20 '18 at 13:05
  • You use `Sum()` in the Pivot query, but you can't sum a date. Try `First()` or `Max()` instead. (I guess this is the problem, but not sure.) – Andre Apr 20 '18 at 13:31
  • @Andre, thanks a lot, Andre, this seemed to solve the problem. I think I learned something new but I'll let the "duplicate" tag on here :-) – RoyH Apr 23 '18 at 06:59

0 Answers0