5

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?

1 Answer 1

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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