Making Row Entries Pair Horizontally in SQL

Question

So this question is similar to one I've asked before, but slightly different.

I'm looking at data for clients who are admitted to and discharged from a program. For each admit and discharge they have an assessment done and are scored on it and sometimes they are admitted and discharged multiple times during a time period.

I need to be able to pair each clients admit score with their following discharge date so I can look at all clients who improved a certain amount from admit to discharge for each of their admits and discharges.

This is an dummy sample of how my data results are formatted right now:

And this is how I'd ideally like it formatted:

But I'd take any point in the right direction or similar formatting help that would allow me to be able to compare all of the instances of admit and discharge scores for all the clients.

Thanks!

Answer 1

In order to get the result, you can apply both the UNPIVOT and the PIVOT functions. The UNPIVOT will convert your multiple columns of date and score into rows, then you can pivot those rows back into columns.

Then unpivot syntax will be similar to this:

select person,
  casenumber,
  ScoreType+'_'+col col,
  value,
  rn
from
(
  select person,
    casenumber,
    convert(varchar(10), date, 101) date,
    cast(score as varchar(10)) score,
    scoreType,
    row_number() over(partition by casenumber, scoretype
                      order by case scoretype when 'Admit' then 1 end, date) rn            
  from yourtable
) d
unpivot
(
  value
  for col in (date, score)
) unpiv

See SQL Fiddle with Demo. This gives a result:

| PERSON | CASENUMBER |             COL |      VALUE | RN |
-----------------------------------------------------------
|    Jon |       3412 |  Discharge_date | 01/03/2013 |  1 |
|    Jon |       3412 | Discharge_score |         12 |  1 |
|     Al |       3452 |      Admit_date | 05/16/2013 |  1 |
|     Al |       3452 |     Admit_score |         15 |  1 |
|     Al |       3452 |  Discharge_date | 08/01/2013 |  1 |
|     Al |       3452 | Discharge_score |         13 |  1 |

As you can see this query also creates the new columns to then pivot. So the final code will be:

select person, casenumber,
  Admit_Date, Admit_Score, Discharge_Date, Discharge_Score
from
(
  select person,
    casenumber,
    ScoreType+'_'+col col,
    value,
    rn
  from
  (
    select person,
      casenumber,
      convert(varchar(10), date, 101) date,
      cast(score as varchar(10)) score,
      scoreType,
      row_number() over(partition by casenumber, scoretype
                        order by case scoretype when 'Admit' then 1 end, date) rn

    from yourtable
  ) d
  unpivot
  (
    value
    for col in (date, score)
  ) unpiv
) src
pivot
(
  max(value)
  for col in (Admit_Date, Admit_Score, Discharge_Date, Discharge_Score)
) piv;

See SQL Fiddle with Demo. This gives a result:

| PERSON | CASENUMBER | ADMIT_DATE | ADMIT_SCORE | DISCHARGE_DATE | DISCHARGE_SCORE |
-------------------------------------------------------------------------------------
|     Al |       3452 | 05/16/2013 |          15 |     08/01/2013 |              13 |
|  Cindy |       6578 | 01/02/2013 |          17 |     03/04/2013 |              14 |
|  Cindy |       6578 | 03/04/2013 |          14 |     03/18/2013 |              12 |
|    Jon |       3412 |     (null) |      (null) |     01/03/2013 |              12 |
|  Kevin |       9868 | 01/18/2013 |          19 |     03/02/2013 |              15 |
|  Kevin |       9868 | 03/02/2013 |          15 |         (null) |          (null) |
|   Pete |       4765 | 02/06/2013 |          15 |         (null) |          (null) |
|  Susan |       5421 | 04/06/2013 |          19 |     05/07/2013 |              15 |

Answer 2

If all the columns you mentioned are in the same table, you can join on same table

 SELECT t1.person,
  t1.caseNumber,
   t1.date adate,
  t1.score  ascore,
  t1.scoreType ascoreType,
  t2.date ddate,
  t2.score  dscore,
  t2.scoreType dscoretype
FROM patient t1
join patient t2
on t1.casenumber=t2.casenumber
and t1.scoreType!=t2.scoreType
and t1.scoreType='Admit'

But this will not show you record of people who have been admitted and not discharged yet. I don't know if you were also looking for that information.

SQL Fiddle link

Hope this helps!

Answer 3

SELECT 
ad.person, ad.CaseNumber, ad.Date as AdmitScoreDate, ad.Score as AdmitScore,
dis.date as DischargeScoreDate, dis.Score as DischargeScore
From
yourTable ad, yourTable dis


WHERE
ad.person=dis.person
and 
ad.ScoreType='Admit'
and d
is.ScoreType='Discharge';