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?