Understanding PIVOT function in T-SQL

Question

I am very new to SQL.

I have a table like 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 I was told to get data like this

ElementID | PhaseID1 | PhaseID5 | PhaseID6 --------------------------------------------     3     |   NULL   |   6.74   |   8.25     4     |   2.23   |   6.8    |   1.5 

I understand I need to use PIVOT function. But can't understand it clearly. It would be great help if somebody can explain it in above case.(or any alternatives if any)

Answer 1

A PIVOT used to rotate the data from one column into multiple columns.

For your example here is a STATIC Pivot meaning you hard code the columns that you want to rotate:

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

Here is a SQL Demo with a working version.

This can also be done through a dynamic PIVOT where you create the list of columns dynamically and perform the PIVOT.

DECLARE @cols AS NVARCHAR(MAX),     @query  AS NVARCHAR(MAX);  select @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.phaseid)              FROM temp c             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')          ,1,1,'')  set @query = 'SELECT elementid, ' + @cols + ' from              (                 select elementid, phaseid, effort                 from temp            ) x             pivot              (                  max(effort)                 for phaseid in (' + @cols + ')             ) p '   execute(@query) 

The results for both:

ELEMENTID   PHASEID1    PHASEID5    PHASEID6 3           Null        6.74        8.25 4           2.23        6.8         1.5