-1

I was trying to create a pivot table in SQL server 2008 and faced this problem. As shown in the image, I have two people with specific primary keys. They received grades A,B,C,D. However, they have different number of grades and also the days represents how many days back they received those grades. So, I am trying to create a pivot table like the second table in the image where grades are ordered in ascending order. Is it possible to achieve that?

This is the image.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
Abhi
  • 3
  • 2
  • Can you show us what you tried so far? If you share your code, even if it is not working, you are more likely to recieve help. – David Rushton Jun 16 '16 at 16:48
  • 1
    In your image, there are up-to 5 columns for an ID. Is it entirely variable? Could there be more than 5? Or is there a maximum number that you could expect? – Nick Jun 16 '16 at 16:50

1 Answers1

0

If you know the number of potential columns, you can use pivot or conditional aggregation -- you just need to establish a row_number to pivot on:

select id_pk, 
  max(case when rn = 1 then code end) code1,
  max(case when rn = 2 then code end) code2,
  max(case when rn = 3 then code end) code3,
  max(case when rn = 4 then code end) code4,
  max(case when rn = 5 then code end) code5
from (
    select *, row_number() over (partition by id_pk order by days) rn
    from yourtable
  ) t
group by id_pk

If you don't know the number of potential columns, you'll need to use dynamic sql. Here's a good example for that: https://stackoverflow.com/a/10404455/1073631

Community
  • 1
  • 1
sgeddes
  • 62,311
  • 6
  • 61
  • 83