Sql-server – Help with a PIVOT

pivotsql-server-2008-r2

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 3×3 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.

Best Answer

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 |

Related Solutions

Sql-server – Need help with SQL Server PIVOT

Try the following:

select office_id,
[1] as value_1, [2] as value_2, 
[3] as value_3, [4] as value_4
from
(
  select office_id, setting_value,
    row_number() over(partition by office_id order by office_id) rnk
  from office_settings
) d
pivot
(
  max(setting_value)
  for rnk in ([1], [2], [3], [4])
) piv;

Or without using PIVOT (generates the same execution plan):

WITH Numbered AS
(
    SELECT 
        OS.office_id,
        OS.setting_value,
        row_num = ROW_NUMBER() OVER (
            -- Note: non-deterministic
            PARTITION BY OS.office_id 
            ORDER BY OS.office_id)
    FROM dbo.office_settings AS OS
)
SELECT
    N.office_id,
    value_1 = MAX(CASE WHEN N.row_num = 1 THEN N.setting_value ELSE NULL END),
    value_2 = MAX(CASE WHEN N.row_num = 2 THEN N.setting_value ELSE NULL END),
    value_3 = MAX(CASE WHEN N.row_num = 3 THEN N.setting_value ELSE NULL END),
    value_4 = MAX(CASE WHEN N.row_num = 4 THEN N.setting_value ELSE NULL END)
FROM Numbered AS N
GROUP BY
    N.office_id;

With dynamic sql (useful when number of pivot columns varies within a search window):

declare @pivcols as varchar(200);

select  @pivcols = stuff((select distinct '],[' + cast(row_number() 
over(partition by office_id order by office_id) as varchar(10))
from office_settings
for xml path('')), 1, 2, '') + ']';

declare @pivquery varchar(500); 

set @pivquery = 'Select office_id, '+   
@pivcols +' 
from   
(select office_id, setting_value, 
 row_number() over(partition by office_id order by office_id) rnk
 from office_settings
) as d
PIVOT
(
max(setting_value)
for rnk in ( '+ @pivcols +' )
) AS pvt ';


EXECUTE(@pivquery);

Sql-server – Using pivot on datetime

I'd go with the classic Cross-Tab approach together with ROW_NUMBER to have "something" to group by:

;
WITH cte as
(
    SELECT ROW_NUMBER() OVER (PARTITION BY Type ORDER By Data) as pos, 
    * 
    FROM -- use your table name instead of the VALUES construct
    (VALUES
    ('2012-01-25 13:48:00.000' ,1),
    ('2012-01-25 13:49:00.000',2),
    ('2012-01-26 13:48:00.000',1),
    ('2012-01-26 13:49:00.000',2)) x(Data,Type)
)
SELECT 
MAX(CASE WHEN Type = 1 THEN Data ELSE NULL END) AS [1],
MAX(CASE WHEN Type = 2 THEN Data ELSE NULL END) AS [2]
FROM cte 
GROUP BY pos

Best Answer

Related Solutions

Sql-server – Need help with SQL Server PIVOT

Sql-server – Using pivot on datetime

Related Question