Convert Rows to columns using 'Pivot' in SQL Server

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.

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 |

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

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 .

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

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)>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,'[',''),']',''),' ',''),'&',''))+
				(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:

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);

select * from (select name, ID from Empoyee) Visits
	pivot(sum(ID) for name
	in ([Emp1],
	[Emp2],
	[Emp3]
	) ) as pivottable;

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

Pivot is one of the SQL operator which is used to turn the unique data from one column into multiple column in the output. This is also mean by transforming the rows into columns (rotating table). Let us consider this table,

If I want to filter this data based on the types of product (Speaker, Glass, Headset) by each customer, then use Pivot operator.

Select CustmerName, Speaker, Glass, Headset  
from TblCustomer  
   Pivot  
    (  
     Sum(Price) for Product in ([Speaker],[Glass],[Headset])  
    ) as PivotTable