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

User · Answer

These are the very basic pivot example kindly go through that.

SQL SERVER – PIVOT and UNPIVOT Table Examples

Example from above link for the product table:

SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
 PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

renders:

 PRODUCT FRED  KATE
 --------------------
 BEER     24    12
 MILK      3     1
 SODA   NULL     6
 VEG    NULL     5

Similar examples can be found in the blog post Pivot tables in SQL Server. A simple sample

User · Answer

Ive something to add here which no one mentioned    The pivot function works great when the source has 3 columns  One for the aggregate  one to spread as columns with for  and one as a pivot for row distribution  In the product example it s QTY  CUST  PRODUCT    However  if you have more columns in the source it will break the results into multiple rows instead of one row per pivot based on unique values per additional column  as Group By would do in a simple query    See this example  ive added a timestamp column to the source table     Now see its impact   SELECT CUST  MILK  FROM Product -- FROM  SELECT CUST  Product  QTY FROM PRODUCT  p PIVOT       SUM QTY  FOR PRODUCT IN  MILK    AS pvt  ORDER BY CUST       In order to fix this  you can either pull a subquery as a source as everyone has done above - with only 3 columns  this is not always going to work for your scenario  imagine if you need to put a where condition for the timestamp    Second solution is to use a group by and do a sum of the pivoted column values again   SELECT  CUST   sum MILK  t MILK  FROM Product PIVOT       SUM QTY  FOR PRODUCT IN  MILK    AS pvt  GROUP BY CUST ORDER BY CUST  GO

User · Answer

To set Compatibility error  use this before using pivot function  ALTER DATABASE  dbname  SET COMPATIBILITY LEVEL   100

User · Answer

FOR XML PATH might not work on Microsoft Azure Synapse Serve  A possible alternative  following  Taryn dynamic generated cols approach  same results is obtained by using STRING AGG  DECLARE  cols AS NVARCHAR MAX    query AS NVARCHAR MAX   SELECT  cols   STRING AGG QUOTENAME c phaseid          OPTIONAL  within group  order by cast t1  FLOW SP SLPM  as INT  asc    FROM  SELECT phaseid FROM temp GROUP BY phaseid  c  set  query    SELECT elementid      cols     from                                 select elementid                  phaseid                  effort                 from temp               x             PIVOT                                max effort                  for phaseid in       cols                    p    execute  query

User · Answer

SELECT  lt non-pivoted column gt        first pivoted column  AS  lt column name gt        second pivoted column  AS  lt column name gt                last pivoted column  AS  lt column name gt  FROM       lt SELECT query that produces the data gt       AS  lt alias for the source query gt  PIVOT        lt aggregation function gt   lt column being aggregated gt   FOR   lt column that contains the values that will become column headers gt       IN    first pivoted column    second pivoted column            last pivoted column     AS  lt alias for the pivot table gt   lt optional ORDER BY clause gt    USE AdventureWorks2008R2   GO SELECT DaysToManufacture  AVG StandardCost  AS AverageCost  FROM Production Product GROUP BY DaysToManufacture       DaysToManufacture          AverageCost 0                          5 0885 1                          223 88 2                          359 1082 4                          949 4105      -- Pivot table with one row and five columns SELECT  AverageCost  AS Cost Sorted By Production Days    0    1    2    3    4  FROM  SELECT DaysToManufacture  StandardCost      FROM Production Product  AS SourceTable PIVOT   AVG StandardCost  FOR DaysToManufacture IN   0    1    2    3    4     AS PivotTable      Here is the result set  Cost Sorted By Production Days    0         1         2           3       4        AverageCost                       5 0885    223 88    359 1082    NULL    949 4105

User · Answer

A pivot is used to convert one of the columns in your data set from rows into columns  this is typically referred to as the spreading column   In the example you have given  this means converting the PhaseID rows into a set of columns  where there is one column for each distinct value that PhaseID can contain - 1  5 and 6 in this case    These pivoted values are grouped via the ElementID column in the example that you have given   Typically you also then need to provide some form of aggregation that gives you the values referenced by the intersection of the spreading value  PhaseID  and the grouping value  ElementID   Although in the example given the aggregation that will be used is unclear  but involves the Effort column   Once this pivoting is done  the grouping and spreading columns are used to find an aggregation value  Or in your case  ElementID and PhaseIDX lookup Effort   Using the grouping  spreading  aggregation terminology you will typically see example syntax for a pivot as   WITH PivotData AS       SELECT  lt grouping column gt             lt spreading column gt             lt aggregation column gt      FROM  lt source table gt    SELECT  lt grouping column gt    lt distinct spreading values gt  FROM PivotData     PIVOT   lt aggregation function gt   lt aggregation column gt           FOR  lt spreading column gt  IN  lt distinct spreading values gt       This gives a graphical explanation of how the grouping  spreading and aggregation columns convert from the source to pivoted tables if that helps further

User · Answer

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

[sql-server] Understanding PIVOT function in T-SQL

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to pivot