If you have an unknown number of values, then you can use a PIVOT with dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ','
+ QUOTENAME('Measurement_' + cast(rn as varchar(10)))
from temptable
cross apply
(
select row_number() over(partition by measure_id order by measurement) rn
from temptable
) x
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT measure_id, ' + @cols + ' from
(
select measure_id, measurement,
''Measurement_''
+ cast(row_number() over(partition by measure_id order by measurement) as varchar(10)) val
from temptable
) x
pivot
(
max(measurement)
for val in (' + @cols + ')
) p '
execute(@query)
See SQL Fiddle With Demo
If you have a known number of values, then you can hard-code the values, similar to this:
SELECT measure_id, [Measurement_1], [Measurement_2],
[Measurement_3], [Measurement_4]
from
(
select measure_id, measurement,
'Measurement_'
+ cast(row_number() over(partition by measure_id order by measurement) as varchar(10)) val
from temptable
) x
pivot
(
max(measurement)
for val in ([Measurement_1], [Measurement_2],
[Measurement_3], [Measurement_4])
) p
See SQL Fiddle With Demo
Both queries will produce the same results:
MEASURE_ID | MEASUREMENT_1 | MEASUREMENT_2 | MEASUREMENT_3 | MEASUREMENT_4
==========================================================================
1 | 2.3 | 3.3 | 3.4 | (null)
2 | 2.3 | 3 | 4 | 4.5
