Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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 |
share|improve this answer
    
Yes. That did it. Thanks. –  vcsjones Oct 11 '13 at 23:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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