Convert Rows to columns using Pivot in SQL Server

Question

I have read the stuff on MS pivot tables and I am still having problems getting this correct   I have a temp table that is being created  we will say that column 1 is a Store number  and column 2 is a week number and lastly column 3 is a total of some type  Also the Week numbers are dynamic  the store numbers are static   Store      Week     xCount -------    ----     ------ 102        1        96 101        1        138 105        1        37 109        1        59 101        2        282 102        2        212 105        2        78 109        2        97 105        3        60 102        3        123 101        3        220 109        3        87   I would like it to come out as a pivot table  like this   Store        1          2          3        4        5        6     -----  101        138        282        220 102         96        212        123 105         37         109   Store numbers down the side and weeks across the top

User · Answer

This is what you can do   SELECT    FROM yourTable PIVOT  MAX xCount          FOR Week in   1   2   3   4   5   6   7    AS pvt   DEMO

User · Answer

Just give you some idea how other databases solve this problem  DolphinDB also has built-in support for pivoting and the sql looks much more intuitive and neat  It is as simple as specifying the key column  Store   pivoting column  Week   and the calculated metric  sum xCount       prepare a 10-million-row table n 10000000 t table rand 100  n    1 as Store  rand 54  n    1 as Week  rand 100  n    1 as xCount     use pivot clause to generate a pivoted table pivot t pivot t   select sum xCount  from t pivot by Store  Week   DolphinDB is a columnar high performance database  The calculation in the demo costs as low as 546 ms on a dell xps laptop  i7 cpu    To get more details  please refer to online DolphinDB manual https   www dolphindb com help index html pivotby html

User · Answer

select   from  select name  ID from Empoyee  Visits     pivot sum ID  for name     in   Emp1        Emp2        Emp3          as pivottable

User · Answer

I m writing an sp that could be useful for this purpose  basically this sp pivot any table and return a new table pivoted or return just the set of data  this is the way to execute it   Exec dbo rs pivot table  schema dbo  table table name  column column to pivot  agg  sum  column to agg   avg  another column to agg               sel cols  column to select1 column to select2 column to select1   new table returned table pivoted    please note that in the parameter  agg the column names must be with     and the parameter must end with a comma      SP  Create Procedure  dbo   rs pivot table       schema sysname dbo       table sysname       column sysname       agg nvarchar max        sel cols varchar max        new table sysname       add to col name sysname null As --Exec dbo rs pivot table dbo   TEMPORAL1 tip liq  sum  val liq   sum  can liq      cod emp cod con tip liq    TEMPORAL1PVT  hola   Begin      Declare  query varchar max          Declare  aggDet varchar 100       Declare  opp agg varchar 5       Declare  col agg varchar 100       Declare  pivot col sysname      Declare  query col pvt varchar max          Declare  full query pivot varchar max          Declare  ind tmpTbl int  --Indicador de tabla temporal 1 tabla temporal global 0 Tabla fisica      Create Table  pvt column          pivot col varchar 100              Declare  column agg table          opp agg varchar 5           col agg varchar 100              IF  EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID  table  AND type in  N U            Set  ind tmpTbl 0      ELSE IF OBJECT ID  tempdb    ltrim rtrim  table    IS NOT NULL         Set  ind tmpTbl 1       IF  EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID  new table  AND type in  N U    OR          OBJECT ID  tempdb    ltrim rtrim  new table    IS NOT NULL     Begin         Set  query  DROP TABLE    new table             Exec   query       End       Select  query  Select distinct    column   From    case when  ind tmpTbl 1 then  tempdb   else    end   schema      table   where    column   is not null        Print  query       Insert into  pvt column pivot col      Exec   query       While charindex      agg 1  gt 0     Begin         Select  aggDet Substring  agg 1 charindex      agg 1 -1            Insert Into  column agg opp agg col agg          Values substring  aggDet 1 charindex      aggDet 1 -1  ltrim rtrim replace substring  aggDet charindex      aggDet 1  charindex      aggDet 1 -4                       Set  agg Substring  agg charindex      agg 1  1 len  agg        End      Declare cur agg cursor read only forward only local static for     Select          opp agg col agg     from  column agg       Open cur agg       Fetch Next From cur agg     Into  opp agg  col agg       While   fetch status 0     Begin          Declare cur col cursor read only forward only local static for         Select              pivot col          From  pvt column           Open cur col           Fetch Next From cur col         Into  pivot col           While   fetch status 0         Begin              Select  query col pvt  isnull    opp agg   case when    column     quotename  pivot col char 39     then    col agg                else null end  0  as    lower Replace Replace  opp agg     convert varchar 100   pivot col      replace replace  col agg                           amp                          case when  add to col name is null then space 0  else     isnull ltrim rtrim  add to col name       end                  print  query col pvt             Select  full query pivot  full query pivot  query col pvt                   --print  full query pivot              Fetch Next From cur col             Into  pivot col                   End               Close cur col          Deallocate cur col           Fetch Next From cur agg         Into  opp agg  col agg       End      Close cur agg      Deallocate cur agg       Select  full query pivot substring  full query pivot 1 len  full query pivot -1        Select  query  Select    sel cols      full query pivot   into    new table   From    case when  ind tmpTbl 1 then  tempdb   else    end        schema      table   Group by    sel cols           print  query      Exec   query    End  GO   This is an example of execution   Exec dbo rs pivot table  schema dbo  table   TEMPORAL1  column tip liq  agg  sum  val liq   avg  can liq      sel cols  cod emp cod con tip liq   new table   TEMPORAL1PVT    then Select   From   TEMPORAL1PVT would return

User · Answer

I ve achieved the same thing before by using subqueries   So if your original table was called StoreCountsByWeek  and you had a separate table that listed the Store IDs  then it would look like this   SELECT StoreID       Week1  SELECT ISNULL SUM xCount  0  FROM StoreCountsByWeek WHERE StoreCountsByWeek StoreID Store StoreID AND Week 1       Week2  SELECT ISNULL SUM xCount  0  FROM StoreCountsByWeek WHERE StoreCountsByWeek StoreID Store StoreID AND Week 2       Week3  SELECT ISNULL SUM xCount  0  FROM StoreCountsByWeek WHERE StoreCountsByWeek StoreID Store StoreID AND Week 3  FROM Store ORDER BY StoreID   One advantage to this method is that the syntax is more clear and it makes it easier to join to other tables to pull other fields into the results too   My anecdotal results are that running this query over a couple of thousand rows completed in less than one second  and I actually had 7 subqueries   But as noted in the comments  it is more computationally expensive to do it this way  so be careful about using this method if you expect it to run on large amounts of data

User · Answer

If you are using SQL Server 2005   then you can use the PIVOT function to transform the data from rows into columns   It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially   First up  here are some quick table definitions and data for use   CREATE TABLE  yt       Store  int      Week  int      xCount  int     INSERT INTO  yt      Store       Week    xCount    VALUES      102  1  96        101  1  138        105  1  37        109  1  59        101  2  282        102  2  212        105  2  78        109  2  97        105  3  60        102  3  123        101  3  220        109  3  87     If your values are known  then you will hard-code the query   select   from      select store  week  xCount   from yt   src pivot     sum xcount    for week in   1    2    3     piv    See SQL Demo  Then if you need to generate the week number dynamically  your code will be   DECLARE  cols AS NVARCHAR MAX        query  AS NVARCHAR MAX   select  cols   STUFF  SELECT       QUOTENAME Week                       from yt                     group by Week                     order by Week             FOR XML PATH      TYPE               value       NVARCHAR MAX              1 1      set  query    SELECT store      cols     from                                 select store  week  xCount                 from yt               x             pivot                                sum xCount                  for week in       cols                    p    execute  query     See SQL Demo   The dynamic version  generates the list of week numbers that should be converted to columns  Both give the same result     STORE     1     2     3   ---------------------------     101   138   282   220       102    96   212   123       105    37    78    60       109    59    97    87

User · Answer

This is for dynamic   of weeks   Full example here SQL Dynamic Pivot  DECLARE  DynamicPivotQuery AS NVARCHAR MAX  DECLARE  ColumnName AS NVARCHAR MAX   --Get distinct values of the PIVOT Column  SELECT  ColumnName  ISNULL  ColumnName             QUOTENAME Week  FROM  SELECT DISTINCT Week FROM  StoreSales  AS Weeks  --Prepare the PIVOT query using the dynamic  SET  DynamicPivotQuery      N SELECT Store       ColumnName          FROM  StoreSales     PIVOT SUM xCount             FOR Week IN       ColumnName       AS PVTTable  --Execute the Dynamic Pivot Query EXEC sp executesql  DynamicPivotQuery

User · Answer

Here is a revision of  Tayrn answer above that might help you understand pivoting a little easier    This may not be the best way to do this  but this is what helped me wrap my head around how to pivot tables   ID   rows you want to pivot  MY KEY   the column you are selecting from your original table that contains the column names you want to pivot   VAL   the value you want returning under each column   MAX VAL     Can be replaced with other aggregiate functions  SUM VAL   MIN VAL   ETC     DECLARE  cols AS NVARCHAR MAX    query  AS NVARCHAR MAX  select  cols   STUFF  SELECT       QUOTENAME MY KEY                   from yt                 group by MY KEY                 order by MY KEY ASC         FOR XML PATH      TYPE           value       NVARCHAR MAX          1 1     set  query    SELECT ID      cols     from                         select ID  MY KEY  VAL              from yt           x         pivot                        sum VAL              for MY KEY in       cols                p            execute  query

[sql] Convert Rows to columns using 'Pivot' in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to pivot

Examples related to pivot-table