No all users do not have to have the same amount. however if UserID 1 has tests A, B, and C and UserID 2 only has tests A and B the row for UserID 2 for test C will have a null unless handled by the case statement as vinu does by replacing with an empty string (which is converted to 0 since its a numeric value, in my sample i converted it to a varchar so its an empty string with no display, how you handle it will be up to you)

As far as the number of columns, Vinu's solution looks at the distinct values across the entire table for LabName. so if you have 10 Users each with 10 different tests (no overlap between the tests performed on each user) you will wind up with 100 columns.

The code below demonstrates what i mean with 2 users and 6 tests.

IF OBJECT_ID('tempdb..#LabValues') IS NOT NULL

DROP TABLE #LabValues

SELECT * INTO #LabValues FROM (VALUES (1,'A',5),(1,'B',6),(1,'C',7),(2,'D',8),(2,'E',9),(2,'F',4))x(UserID,LabName,ResultValue)

Declare @sql Nvarchar(MAX)

;With CTE

As

(

Select Distinct LabName From #LabValues

)

Select @sql = STUFF((Select ',MAX(Case When LabName = ' + CHAR(39) + LabName + CHAR(39) + ' Then CAST(ResultValue AS VARCHAR) Else '''' End) AS ' + CHAR(39) + LabName + CHAR(39) From CTE For XML Path('')),1,1,'')

Select @sql = 'Select UserId, ' + @sql + ' From #LabValues Group By UserId'

Execute (@sql)