Q:

Making Row Entries Pair Horizontally in SQL

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!

A:

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 |

