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);
You need to think about your partitions as if they are separate tables.
A clustered index on a partitioned table effectively has the partition number as its first key. For you, with a partition per unique PartitionKey, the clustered index will essentially have that column in the front of it. Or as good as...
I'd expect having Id first would feel better, in case you don't know the month and need to search all partitions. It'll seek on each partition rather than scan them.
Best Answer
Look for this solution: