0

I have a view that gives data which looks like this:

        Rank        |        Score
        --------------------------
        1           |            1
        2           |          2.5
        3           |            5
        1           |            7
        2           |          2.5
        3           |            8
        1           |            9
        2           |          2.5
        3           |            5

What I would like to do is apply a PIVOT so that it appears in a 3x3 matrix. The result is always guaranteed to have 9 rows, with RANK going 123..123..123.

       1   |   2   |   3
    ------------------------
       1      2.5      5
       7      2.5      8
       9      2.5      5

I've attempted to to use a PIVOT, but have been unsuccessful (the part about the aggregate function always confuses me):

SELECT
    *
FROM
    MyView
PIVOT
(
    MAX([Rank]) FOR [Rank] IN ([1], [2], [3])
) pvt

How can I shape my data into the form that I am looking for (PIVOT or otherwise)?

Sorry if the requirement is a bit odd, I'm working with a piece of reporting software that does what I want it to do, if I can shape my data into that form.

2

You are on the right track with your current query but I would alter it slightly to use a subquery and include a row_number() to give a distinct value to each score in your rank.

If you query your data using:

select [rank], score,
  row_number() over(partition by [rank] order by [rank]) rn
from yourtable

See Demo

You will get a sequenced number for each score inside your ranks. This sequence number will then be grouped by in your PIVOT making your full code:

select [1], [2], [3]
from
(
  select [rank], score,
    row_number() over(partition by [rank] order by [rank]) rn
  from yourtable
) d
pivot
(
  max(score)
  for [rank] in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. This query gives you a result:

| 1 |   2 | 3 |
|---|-----|---|
| 1 | 2.5 | 5 |
| 7 | 2.5 | 8 |
| 9 | 2.5 | 5 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.