SQL Pivot on Int for String

Question

Trying to manipulate some SQL with a Pivot however what I need to pivot on is text and I cant just get it working.

customerID  appointment             appointmentNumber   treatment
9           2014-01-09 09:30:00.000 1                   Osteo F 45
9           2014-01-20 09:00:00.000 2                   Osteo F 45
9           2014-01-30 09:30:00.000 3                   Osteo F 45
9           2014-02-10 09:00:00.000 4                   Osteo F 45

What I need is to turn the column "appointmentNumber" into column headers, with the "treatment" shown below...

customerID 1 2 3 4 etc...

I have always used Pivot tables on strings for numbers (sums, counts) in the past and never on numbers for strings so I am a little lost.

I had a look at SQL Pivot with String but cant seem to apply this to my own code.

Any ideas?

show us the code you tried and where you get an error. – Hogan Dec 19 '14 at 15:48 — Hogan, Dec 19 '14 at 15:48
did you try to use the first() function? – Beth Dec 19 '14 at 15:48 — Beth, Dec 19 '14 at 15:48

dotjoe · Answer 1 · 2014-12-19T16:03:26.413

1

You can choose between max/min aggregate functions for a string column. In this case I'd assume appointmentNumber, customerId are unique so the aggregate function isn't really doing anything except taking the first value.

select
    *
from
    table t
    pivot (
        max(treatment)
        for appointmentNumber in ([1],[2],[3],[4],[5])
    ) p

edited Dec 19 '14 at 16:03

answered Dec 19 '14 at 15:56

dotjoe

26,242
5
63
77

score 0 · Answer 2 · answered Dec 19 '14 at 15:51

You can use conditional aggregation with max():

select customerId,
       max(case when appointmentnumber = 1 then treatement end) as an_1,
       max(case when appointmentnumber = 2 then treatement end) as an_2,
       max(case when appointmentnumber = 3 then treatement end) as an_3,
       max(case when appointmentnumber = 4 then treatement end) as an_4,
       max(case when appointmentnumber = 5 then treatement end) as an_5
from table t
group by customerid;

You can also do this using pivot if you like. max() works fine on string variables.

SQL Pivot on Int for String

2 Answers2

Linked