-- first our customer masterfile (yeah I know it is pretty ordinary)
CREATE TABLE tst_Customer_Master (
TCM_ID INT Identity Primary Key Clustered,
TCM_Customer varchar(60),
TCM_Salesman varchar(60))
GO
-- then some customer sales activity
CREATE TABLE tst_Customer_Sales (
TCS_ID INT Identity Primary Key Clustered,
TCM_ID int,
TCS_Date DATETIME,
TCS_Quantity INT,
TCS_Value MONEY )
GO
-- now let's populate our tables
INSERT tst_Customer_Master (TCM_Customer, TCM_Salesman)
SELECT * FROM (
SELECT 'Customer 1' as customer,'Salesman 1'as salesman union all
SELECT 'Customer 2','Salesman 1' union all
SELECT 'Customer 3','Salesman 2' union all
SELECT 'Customer 4','Salesman 2' union all
SELECT 'Customer 5','Salesman 3' ) as src
GO
INSERT tst_Customer_Sales (TCM_ID, TCS_Date, TCS_Quantity, TCS_Value)
SELECT * FROM (
SELECT '1' as Customer,'20100101' as Date, 11 as Qty, 1001.00 as Val union all
SELECT '1','20100201',12, 1002.00 union all
SELECT '1','20100301',13, 1003.00 union all
SELECT '1','20100401',14, 1004.00 union all
SELECT '2','20100101',21, 2001.00 union all
SELECT '2','20100201',22, 2002.00 union all
SELECT '2','20100301',23, 2003.00 union all
SELECT '2','20100401',24, 2004.00 union all
SELECT '3','20100101',31, 3001.00 union all
SELECT '4','20100201',42, 4002.00 union all
SELECT '5','20100301',53, 5003.00 ) as src
GO
-- notice I do not mention the Identity Column - SQL will manage that for me
-- notice the yyyymmdd "style 112" format - implicitly converts to datetime
CREATE PROCEDURE dbo.uPivot (
@sourcedata varchar(8000),
@Pivot_Over_Source_Column varchar(2000),
@pivot_value_column varchar(2000),
@Pivot_Value_Aggregates varchar(2000),
@pivot_value_format varchar(500) = NULL)
AS
/*
@sourcedata is any legitimate select statement
@Pivot_Over_Source_Column is the column(s) that define the row
@pivot_value_column is the source of data giving the new column names
@Pivot_Value_Aggregates is what the individual cells will contain NB mandatory square brackets immediately after each aggregate
@pivot_value_format is used as the convert style for @pivot_value_column with the additions of 'day' and 'month' and 'year'
-----------------------------+-----------------------+-----------------------+
Pivot_Over_Source_Column |Pivot_Value_Column 1 |Pivot_Value_Column 2 |
-----------------------------+-----------------------+-----------------------+
row1 |Pivot_Value_Aggregates |Pivot_Value_Aggregates |
-----------------------------+-----------------------+-----------------------+
row1 |Pivot_Value_Aggregates |Pivot_Value_Aggregates |
-----------------------------+-----------------------+-----------------------+
Now those aggregates in @Pivot_Value_Aggregates (yes there can be more than one)
must be expressed as a legit aggregate followed by [] or [suffix] (no spaces)
e.g. 1) sum(my_column)[]
2) count(my_column)[_count], max(my_column)[_max]
The first example will simply have the headings as defined by @pivot_value_column
The second will have two columns the first as per @pivot_value_column plus the suffix '_count'
the second column as per @pivot_value_column plus the suffix '_max'
*/
Set nocount on
declare @sql varchar(8000)
declare @columns varchar(8000)
declare @id int
-- we need to use a few temp tables to unpack our delimited list of columns.
-- while these temp tables will survive just within this stored procedure,
-- I have an old habit of dropping first - mainly because I write these things initially just as T-SQL code then turn it into a SP
If object_id('tempdb..#pivot_column_name','U') is not null drop table #pivot_column_name
If object_id('tempdb..#pivot_column_style','U') is not null drop table #pivot_column_style
If object_id('tempdb..#pivot_column_data','U') is not null drop table #pivot_column_data
create table #pivot_column_name (id int identity,pivot_column_name varchar(255))
create table #pivot_column_data (id int identity,pivot_column_name varchar(255),pivot_column_data varchar(255),pivot_column_donor varchar(255))
create table #pivot_column_style (id int identity,pivot_column_style varchar(5))
-- now unpack our delimited list of pivot columns
insert into #pivot_column_name (pivot_column_name)
select substring(pivot_value_column,number,charindex(',',pivot_value_column+',',number)-number)
from (select @pivot_value_column as pivot_value_column) p
cross join (select number from master..spt_values where type = 'p') n
where substring(','+pivot_value_column,number,1) = ','
and number <= len(pivot_value_column)
-- and unpack out delimited list of formats needed for those columns
insert into #pivot_column_style (pivot_column_style)
select substring(pivot_column_style,number,charindex(',',pivot_column_style+',',number)-number)
from (select isnull(@pivot_value_format,'') as pivot_column_style) p
cross join (select number from master..spt_values where type = 'p') n
where substring(','+pivot_column_style,number,1) = ','
and number <= len(pivot_column_style)
-- now we can examine our source data to get the unique values for the new columns
Select @id=min(id) from #pivot_column_name
While @id>0
Begin
Select @pivot_value_column=pivot_column_name from #pivot_column_name where id = @id
set @pivot_value_format = isnull((select pivot_column_style from #pivot_column_style where id = @id),'')
set @sql = 'select distinct ''' + -- next column is the definition we need to use when comparing the actual data in the final query
case when isnumeric(isnull(@pivot_value_format,'')) = 1 then 'case when convert(varchar(255),' + @pivot_value_column +','+@pivot_value_format +')'
when isnull(@pivot_value_format,'') = 'Day' then 'case when datename(Day,' + @pivot_value_column +')'
when isnull(@pivot_value_format,'') = 'Month' then 'case when datename(Month,' + @pivot_value_column +')'
when isnull(@pivot_value_format,'') = 'Year' then 'case when datename(Year,' + @pivot_value_column +')'
else 'case when convert(varchar(255),' + @pivot_value_column + ')'
end
+''','+ -- next column is the data we need to use when comparing the actual data in the final query
case when isnumeric(isnull(@pivot_value_format,'')) = 1 then ''', convert(varchar(255),' + @pivot_value_column +','+@pivot_value_format +')'
when isnull(@pivot_value_format,'') = 'Day' then ' datename(Day,' + @pivot_value_column +')'
when isnull(@pivot_value_format,'') = 'Month' then ' datename(Month,' + @pivot_value_column +')'
when isnull(@pivot_value_format,'') = 'Year' then ' datename(Year,' + @pivot_value_column +')'
else ' convert(varchar(255),' + @pivot_value_column + ')'
end
+','+ @pivot_value_column
+' from ('+ @sourcedata +') srce order by '+@pivot_value_column
insert into #pivot_column_data(pivot_column_name,pivot_column_data,pivot_column_donor)
exec (@sql)
Select @id=min(id) from #pivot_column_name where id > @id
end
-- Because of dates we need to keep the original datetime to get correct chronology, but that could cause dupes, so no must remove any dupes from our column list
delete #pivot_column_data -- kill any dupes
where exists (select * from #pivot_column_data d2 where d2.id > #pivot_column_data.id and d2.Pivot_column_name = #pivot_column_data.pivot_column_name and d2.pivot_column_data = #pivot_column_data.pivot_column_data)
-- with a distinct list of new columns, we can now construct the aggregate values for each of the columns
select @columns = isnull(@columns+',',',') + replace(replace( @pivot_value_aggregates,'(','(' + Pivot_Column_name +' =''' + pivot_column_data + ''' THEN ' ),')[', ' ELSE '''' END) as [' + pivot_column_data )
from #pivot_column_data
-- with the columns fully defined, it becomes a fairly simple group by
set @sql = 'select ' + @pivot_over_source_column+@columns + ' from ('+ @sourcedata +') srce GROUP BY ' + @pivot_over_source_column
exec (@sql)
-- now clean up - unnecessary inside a stored procedure, but again that old habit...
if object_id('tempdb..#pivot_column_name','U') is not null drop table #pivot_column_name
if object_id('tempdb..#pivot_column_data','U') is not null drop table #pivot_column_data
If object_id('tempdb..#pivot_column_style','U') is not null drop table #pivot_column_style
-- and that is that.
GO
uPivot
'select tcm_customer,tcm_salesman, s.*
from tst_customer_master M
inner join tst_Customer_Sales S on M.tcm_id = S.tcm_id', -- our query
'TCM_customer', -- the rows will be for each customer
'TCS_Date', -- we want months as columns
'sum(tcs_quantity)[]', -- we will aggregate quanity
'Month' -- our columns should display month names
uPivot
'select tcm_customer,tcm_salesman, s.*
from tst_customer_master M
inner join tst_Customer_Sales S on M.tcm_id = S.tcm_id', -- our query
'TCM_customer', -- the rows will be for each customer
'TCS_Date', -- we want months as columns
'sum(tcs_quantity)[_QTY],sum(tcs_value)[_SALES]', -- we will aggregate quanity
'Month' -- our columns should display month names
uPivot
'select tcm_customer,tcm_salesman, s.*
from tst_customer_master M
inner join tst_Customer_Sales S on M.tcm_id = S.tcm_id', -- our query
'TCM_customer', -- the rows will be for each customer
'TCS_Date,TCS_Date', -- we want months as columns AND years
'sum(tcs_quantity)[_QTY],sum(tcs_value)[_SALES]', -- we will aggregate quanity AND sales value
'Month,Year' -- our columns should display month names or years
create view vw_custsales
as
select tcm_customer as customer
,tcm_salesman as salesman
,tcs_date as dt
,tcs_quantity as qty
,tcs_value as sales
from tst_customer_master M
inner join tst_Customer_Sales S on M.tcm_id = S.tcm_id
GO
-- also helps if you have indexes on those columns you want to pivot on
create index idx_tst_customer_sales_date on tst_customer_sales (tcs_date)
create index idx_tst_customer_master_customer on tst_customer_master (tcm_customer)
GO
-- now lets try the pivot one last time using our view
uPivot
'select * from vw_custsales', -- our query
'customer', -- the rows will be for each customer
'dt,dt', -- we want months as columns AND years
'sum(qty)[ #],sum(sales)[ $]', -- we will aggregate quanity AND sales value
'Month,Year' -- our columns should display month names or years
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
You can also added a suppress-zeroes facility by replacing line 108 with this...
Open in new window
... where I used NULL in the inner part (instead of the original empty string) and added the ISNULL (or COALESCE) and CAST to output blanks instead of the NULLsI added a new parameter to allow this to be chosen by the caller.
You can also sort by the rows by adding an "ORDER BY @Pivot_over_Source_column"
It also works nicely if you add WITH ROLLUP at line 113 to get column totals, using an ISNULL (or COALESCE) with 'zz' to put the total at the bottom if you added the sort clause.
In short - thanks for a nice cross-tab solution, it's helped me with an immediate reporting need!
Author
Commented:Thanks for the feedback and for the additional insights.
Apologies for not acknowledging your comment earlier, was looking at extending the routines for a MTD and YTD requirement and only just noticed your comment.
Cheers,
Mark
Commented:
I noticed that you used master..spt_values
why will it work with Month,Day, Year, But not Quarter
I have some commented code I added but it was to analyze
Open in new window