sql server - Understanding PIVOT function in T-SQL -

sql server - Understanding PIVOT function in T-SQL -

i new sql.

i have table this:

id | teamid | userid | elementid | phaseid | effort ----------------------------------------------------- 1 | 1 | 1 | 3 | 5 | 6.74 2 | 1 | 1 | 3 | 6 | 8.25 3 | 1 | 1 | 4 | 1 | 2.23 4 | 1 | 1 | 4 | 5 | 6.8 5 | 1 | 1 | 4 | 6 | 1.5

and told info this

elementid | phaseid1 | phaseid5 | phaseid6 -------------------------------------------- 3 | null | 6.74 | 8.25 4 | 2.23 | 6.8 | 1.5

i understand need utilize pivot function. can't understand clearly. great help if can explain in above case.(or alternatives if any)

a pivot used rotate info 1 column multiple columns.

for illustration here static pivot meaning hard code columns want rotate:

create table temp ( id int, teamid int, userid int, elementid int, phaseid int, effort decimal(10, 5) ) insert temp values (1,1,1,3,5,6.74) insert temp values (2,1,1,3,6,8.25) insert temp values (3,1,1,4,1,2.23) insert temp values (4,1,1,4,5,6.8) insert temp values (5,1,1,4,6,1.5) select elementid , [1] phaseid1 , [5] phaseid5 , [6] phaseid6 ( select elementid, phaseid, effort temp ) x pivot ( max(effort) phaseid in([1], [5], [6]) )p

here sql fiddle working version.

this can done through dynamic pivot create list of columns dynamically , perform pivot.

declare @cols nvarchar(max), @query nvarchar(max); select @cols = stuff((select distinct ',' + quotename(c.phaseid) temp c xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') set @query = 'select elementid, ' + @cols + ' ( select elementid, phaseid, effort temp ) x pivot ( max(effort) phaseid in (' + @cols + ') ) p ' execute(@query)

the results both:

elementid phaseid1 phaseid5 phaseid6 3 null 6.74 8.25 4 2.23 6.8 1.5

sql-server sql-server-2008 tsql pivot