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 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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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);
share|improve this answer
    
If setting_value has an X (unknown) amount of values, is there a way to code this without using a complex dynamic SQL query? Or when using pivots, must one assume a pre-determined number of values to pivot against? –  beeks Apr 23 at 2:43
1  
You need to use dynamic SQL if count of setting_value changes over a given pivot window (in this case office_id). I have updated the query for this case too. –  davisdba Apr 23 at 2:56
    
In my case, I happen to know exactly how many setting_value rows there will be per office_id so I don't need it to be dynamic. –  jamauss Apr 23 at 6:22
    
Thanks Binaya - I was able to get that to work great! –  jamauss Apr 23 at 6:55
    
Paul, I do happen to have a primary key on the office_settings table, I just didn't include it in my question because I didn't realize it was relevant. How would I modify Binaya's SQL (the first bit) to ensure the values get pivoted in the same way on each output row? –  jamauss Apr 23 at 7:55

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.