Need help with SQL Server PIVOT

Question

I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations and I have the following:

TABLE office_settings
column office_id (bigint not null)
column setting_value (NVARCHAR(500) NOT NULL)
office_id setting_value
--------------------------------------------
4 0.2
4 0.2
4 2
4 3
5 0.2
5 0.2
5 2
5 3

I'd like to convert this using PIVOT (assuming that's the right tool for the job) to this, basically "grouping by" the office_id column, so:

office_id value_1 value_2 value_3 value_4
------------------------------------------------------
 4 0.2 0.2 2 3
 5 0.2 0.2 2 3

I should also mention that I need to CAST the setting_value column values to DECIMAL(4,2) in the process of this - in case that has any bearing on the T-SQL required to accomplish it.

Any ideas?

Answer 1

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);