CREATE TABLE tst_CustSales (
TCS_ID INT Identity Primary Key Clustered,
TCS_Customer varchar(60),
TCS_Date DATETIME,
TCS_Quantity INT,
TCS_Value MONEY )
GO
CREATE TABLE tst_EAV_Data (
TED_ID INT Identity Primary Key Clustered,
TED_Entity varchar(60),
TED_Attribute varchar(60),
TED_Value varchar(60) )
GO
-- now let's populate our tst_* tables
INSERT tst_CustSales (TCS_Customer, TCS_Date, TCS_Quantity, TCS_Value)
SELECT * FROM (
SELECT 'Customer 1' as Customer,'20090101' as Date, 11 as Qty, 1001.00 as Val union all
SELECT 'Customer 1','20090201',12, 1002.00 union all
SELECT 'Customer 1','20090301',13, 1003.00 union all
SELECT 'Customer 1','20090401',14, 1004.00 union all
SELECT 'Customer 2','20090101',21, 2001.00 union all
SELECT 'Customer 2','20090201',22, 2002.00 union all
SELECT 'Customer 2','20090301',23, 2003.00 union all
SELECT 'Customer 2','20090401',24, 2004.00 union all
SELECT 'Customer 3','20090101',31, 3001.00 union all
SELECT 'Customer 4','20090201',42, 4002.00 union all
SELECT 'Customer 5','20090301',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
-- now our EAV table, again imagine some diverse attributes
INSERT tst_EAV_Data (TED_Entity, TED_Attribute, TED_Value)
SELECT * FROM (
SELECT 'Customer 1' as Customer,'Phone' as Attr,'+61299991234' as Data_Val union all
SELECT 'Customer 1','Address','24 Somewhere Street' union all
SELECT 'Customer 1','Building','The ReallyTall One' union all
SELECT 'Customer 1','Contact','Marcus Aurelius' union all
SELECT 'Customer 2','Phone','+61288881234' union all
SELECT 'Customer 2','Contact','Ritesh Shah' union all
SELECT 'Customer 3','Address','1600 Pennsylvania Avenue' union all
SELECT 'Customer 3','Building','The WhiteHouse' union all
SELECT 'Customer 4','Phone','+61277771234' union all
SELECT 'Customer 4','Address','1 Nile Way' union all
SELECT 'Customer 4','Building','The Pyramids' union all
SELECT 'Customer 4','Contact','Cleo Patra' union all
SELECT 'Customer 5','Phone','+61277771222' union all
SELECT 'Customer 5','Friend','Cleo Patra' ) as src
GO
SELECT TCS_Customer, [01 Feb 2009],[01 Mar 2009],[01 Apr 2009]
FROM
(select TCS_Customer, TCS_Date, TCS_Value from tst_CustSales ) sourcedata
PIVOT
(sum(TCS_Value) for TCS_Date in ([01 Feb 2009],[01 Mar 2009],[01 Apr 2009])) pivottable
GO
DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)
SET @Columns = substring((select ',['+TED_Attribute+']' from tst_EAV_Data group by TED_Attribute for xml path('')),2,8000)
SET @SQL = 'SELECT * FROM
(Select TED_Entity as Cust,TED_Attribute,TED_Value from tst_EAV_Data) sourcedata
PIVOT
(max(TED_Value) for TED_Attribute in ('+@Columns+')) pivottable'
EXEC(@sql)
GO
CREATE PROCEDURE uDynamicPivot(
@sourcedata varchar(8000),
@Pivot_On_Source_Column varchar(2000),
@Pivot_Value_Aggregate varchar(10),
@Pivot_Value_Column varchar(2000),
@Pivot_Column_List varchar(2000),
@Pivot_Column_Style_Code varchar(4)) -- used in convert for style code
AS
BEGIN
-- we really should put in some error checking, e.g. if anything is NULL it will crash.
declare @columns varchar(max)
declare @sql nvarchar(max)
set @sql = N'set @columns = substring((select '', [''+convert(varchar,'+@Pivot_Column_List+@Pivot_Column_Style_Code+')+'']'' from '+@sourcedata+' group by '+@Pivot_Column_List+' for xml path('''')),2,8000)'
execute sp_executesql @sql,
N'@columns varchar(max) output',
@columns=@columns output
set @sql = N'SELECT * FROM
(SELECT '+@Pivot_On_Source_Column+','+@Pivot_Column_List+','+@Pivot_Value_Column+' from '+@sourcedata+') src
PIVOT
('+@Pivot_Value_Aggregate+'('+@Pivot_Value_Column+') FOR '+@Pivot_Column_List+' IN ('+@columns+') ) pvt
ORDER BY 1'
execute sp_executesql @sql
END
GO
CREATE VIEW vw_last_3_months AS
SELECT TCS_Customer as Customer
, TCS_Value
, DATEADD(day, DATEDIFF(day, 0, TCS_Date),0) as Date
, DATEADD(month, DATEDIFF(month, 0, TCS_Date),0) as Start_Of_Month
, DATEADD(month, DATEDIFF(month, -1, TCS_Date), -1) as End_Of_Month
FROM tst_CustSales
WHERE TCS_Date >= convert(varchar(6),dateadd(month,-3,getdate()),112)+'01'
GO
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 (15)
Commented:
But, I followed your steps, I dont have a complex query, I was just wondering how pivot tables affect performance, your answer was very satisfying.
I'll drop a visit to your profile either way, sure it worth it.
Thanks again
Commented:
Is it possible to also add a sum of al columns as a seperate ´TOTAL´ column?
Author
Commented:Would add in another definition for the totals (similar to @columns).
But then we can no longer simply cheat with "select * from" we would have to add in that new computed column, and probably a parameter to decide when / if to use.
If you are feeling adventurous, there is a slightly more involved Article that should be able to do that for you : https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_4256-Dynamic-Pivot-Procedure-without-the-Pivot-function.html
Cheers,
Mark
Commented:
Commented:
View More