December 12, 2011 at 12:30 pm
Need help in pivot and sp_transform .
I have a stored proc to calculate the no of people contacted by a representative in a month.
then we generate a report to display the data.
i am using the sp_transform to display the data.
rep uid sep1 2011 12.00AM oct1 2011 12.00AM aug1 2011 12.00AM dec1 2011 12.00AM total
jo 566 4 2 1 5 12
amy 345 2 1 5 3 11
total 6 3 6 8 23[/b]
the above example in sep1--uid 566 contacted 4 people and so on
when i give the month
EXEC usp_GetCountForReps 1001, '08/01/2011', 'mm', 12
for this date i need to show next 12 months.
but the problem i am facing here is the order on the final report is not showing the months in order.
can some one suggest how to get them in order month wise.
like sep 2011,oct 2011,nov 2011,dec2011,jan2012,feb2012,mar2012
December 13, 2011 at 4:03 pm
Hello,
How can we get the month names in order based on dynamic generation using the above method.
Any help is greatly appreciated
December 13, 2011 at 4:52 pm
sort by the month datepart integer, but display the month name
December 14, 2011 at 10:50 am
Instead of use a datetime field, you can use a int field to store the month and year in format [yyyymm] before exec sp_transform:
select DATEPART(yyyy, dateField) * 100 + DATEPART(mm, dateField) as groupField,
COUNT(IdSale) as countField
from Sales
group by DATEPART(yyyy, dateField) * 100 + DATEPART(mm, dateField)
I hope this be useful.
December 14, 2011 at 10:58 am
Can you post the code in the stored procedure?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 22, 2011 at 3:04 pm
As suggested i have placed the code used for this issue.
Since the date is genrated dynamically based on the specified date for next 6 months or so, we need the order of the dates to show up properly on the final table.
Any Suggestions/help is appreciated.
---create a table with consolidated data from diff sources
CREATE TABLE Results
(
UserID int NOT NULL,
EffectiveDate datetime NOT NULL,
MemberCount int NOT NULL,
RepName varchar(100),
DisplayOrder int default 0
)
--insert the test data
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Jun 1 2009 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Jul 1 2009 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Aug 1 2009 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Sep 1 2009 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Oct 1 2009 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Nov 1 2009 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Dec 1 2009 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Jan 1 2010 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Feb 1 2010 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Mar 1 2010 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,Apr 1 2010 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 353910,May 1 2010 12:00AM,0,'Rep_DD',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Jun 1 2009 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Jul 1 2009 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Aug 1 2009 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Sep 1 2009 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Oct 1 2009 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Nov 1 2009 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Dec 1 2009 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Jan 1 2010 12:00AM,4,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Feb 1 2010 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Mar 1 2010 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,Apr 1 2010 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 350659,May 1 2010 12:00AM,0,'Rep_RE',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Jun 1 2009 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Jul 1 2009 12:00AM,1,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Aug 1 2009 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Sep 1 2009 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Oct 1 2009 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Nov 1 2009 12:00AM,2,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Dec 1 2009 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Jan 1 2010 12:00AM,28,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Feb 1 2010 12:00AM,1,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Mar 1 2010 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,Apr 1 2010 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select 250689,May 1 2010 12:00AM,0,'Rep_CS',0
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Jun 1 2009 12:00AM,28,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Jul 1 2009 12:00AM,58,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Aug 1 2009 12:00AM,33,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Sep 1 2009 12:00AM,34,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Oct 1 2009 12:00AM,24,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Nov 1 2009 12:00AM,55,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Dec 1 2009 12:00AM,30,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Jan 1 2010 12:00AM,1457,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Feb 1 2010 12:00AM,65,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Mar 1 2010 12:00AM,9,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,Apr 1 2010 12:00AM,6,'ZZTotal',99
insert results ( UserID,EffectiveDate,MemberCount,RepName,DisplayOrder ) select -1,May 1 2010 12:00AM,0,'ZZTotal',99
--the actual place where we do the pivot and get teh final result
DECLARE @s-2 varchar(8000)
SELECT @s-2 = ''
SELECT @s-2 = 'CREATE TABLE #TR(RepUserID int, RepName varchar(200), Total int '
EXEC sp_Transform @Aggregate_Column='MemberCount', @TableOrView_Name='#Results',
@Select_Column='RepName, UserID', @Pivot_Column='EffectiveDate', @Total = 0,
@Order_By = 'RepName'
the proc used:
create proc usp_GetMemberCountForReps (
@user-id INT,
@EffectiveDate varchar(10),
@Division varchar(10),
@Number int
)
begin
DECLARE
DECLARE @Date datetime
SELECT @Date = DATEADD(mm, DATEDIFF(mm,0,@EffectiveDate), 0)
set @user-id=105
IF OBJECT_ID('Results') IS NOT NULL
DROP TABLE Results
CREATE TABLE Results
(
-- columns returned by the function
UserID int NOT NULL,
--EffectiveDate varchar(10) NOT NULL,
EffectiveDate datetime NOT NULL,
MemberCount int NOT NULL,
RepName varchar(100),
DisplayOrder int default 0
)
Declare @pastNMonths int
SET @pastNMonths = -8
--First Day of Nth month in the past
SELECT @Date = DATEADD(mm, DATEDIFF(mm,0,getdate())+@pastNMonths, 0)
PRINT Convert(varchar(10), @Date, 101)
IF EXISTS ( SELECT 1 FROM dbo.Lookup_Clauses INNER JOIN dbo.Lookup_Clients ON dbo.Lookup_Clauses.ClauseCode in ('OperationsGroupType2')
AND dbo.Lookup_Clients.ClientName IN (SELECT VariableValue FROM dbo.Application_Variables WHERE VariableName='HealthPlanName')INNER JOIN
dbo.MapClientsToClauses MC ON MC.clientid=dbo.Lookup_Clients.ClientId AND dbo.Lookup_Clauses.ClauseId=MC.ClauseId)
BEGIN
INSERT INTO Results(UserID, EffectiveDate,MemberCount, RepName)
SELECT UserID, Convert(varchar(10), EffectiveDate, 101), MemberCount, RepName
FROM dbo.getmembercountbyeffectivedate(@UserID, Convert(varchar(10), @Date, 101), @Division, @Number)
WHERE userid NOT IN (SELECT userid FROM dbo.User_Details WHERE FirstName='Gene' AND LastName='West' AND username IS NOT NULL)
END
ELSE
BEGIN
INSERT INTO Results(UserID, EffectiveDate,MemberCount, RepName)
SELECT UserID, Convert(varchar(10), EffectiveDate, 101), MemberCount, RepName
FROM dbo.getmembercountbyeffectivedate(@UserID, Convert(varchar(10), @Date, 101), @Division, @Number)
END
--Insert Row For Total(Horiz)
INSERT INTO Results(UserID, EffectiveDate,MemberCount, RepName, DisplayOrder)
SELECT -1, Convert(varchar(10), A.EffectiveDate, 101), 0, 'ZZTotal', 99
FROM (SELECT DISTINCT EffectiveDate FROM Results(NOLOCk)) A
--SELECT Sum(r1.MemberCount) FROM #Results r1(NOLOCK)
--WHERE Convert(varchar(101), r1.EffectiveDate, 101) = '07/01/2006' AND r1.UserID = -1
--SELECT * FROM #Results
PRINT 'before update'
--Update Totals
UPDATE Results
SET MemberCount = (SELECT Sum(r1.MemberCount) FROM Results r1(NOLOCK)
WHERE Results.EffectiveDate = r1.EffectiveDate)
WHERE Results.UserID = -1
PRINT 'after update'
--SELECT * FROM #Results
--UPDATE #Results SET RepName = dbo.GetName(UserID, 'FL')
DECLARE @count int
SELECT @count = COUNT(*) FROM Results
SELECT * FROM Results
IF (@count > 0 )
BEGIN
DECLARE @s-2 varchar(8000)
SELECT @s-2 = ''
SELECT @s-2 = 'CREATE TABLE #TR(RepUserID int, RepName varchar(200), Total int '
EXEC sp_Transform @Aggregate_Column='MemberCount', @TableOrView_Name='#Results',
@Select_Column='RepName, UserID', @Pivot_Column='EffectiveDate', @Total = 0,
@Order_By = 'RepName'
END
END
---the proc which gets called
exec usp_GetMemberCountForReps 105 ,'01/01/2012','mm', 12
if we give 12 , it should show 12 months in order,
if 6 then six months in order from the date '01/01/2012' given dynamically when calling the proc
the result should be like
Repnameuserid Jan 1 2012 Dec 1 2011 Nov 1 2011 Oct1 2011 Sep 1 2011 Aug 1 2011 Total REp_dd 12321610313
Rep_cs 345 148221
But right now, the order of the dates is not coming properly.
like Aug2011 Dec2012 Jan 2011
December 22, 2011 at 4:39 pm
In the following link I found an implementation of sp_Transform that may be is different of your implementation, but it gives the result that you're expecting:
http://www.itrain.de/knowhow/sql/tsql/pivot/sp_transform_v1_1.asp
I tested it with this code against your data sample:
EXEC sp_Transform @Aggregate_Function='SUM', @Aggregate_Column='MemberCount', @TableOrView_Name='results',
@Select_Column='RepName', @Pivot_Column='EffectiveDate', @Debug = 1
And this was the result:
[p]RepNameJun 1 2009 12:00AMJul 1 2009 12:00AMAug 1 2009 12:00AMSep 1 2009 12:00AMOct 1 2009 12:00AMNov 1 2009 12:00AMDec 1 2009 12:00AMJan 1 2010 12:00AMFeb 1 2010 12:00AMMar 1 2010 12:00AMApr 1 2010 12:00AMMay 1 2010 12:00AMTotal
Rep_CS010002028100032
Rep_DD0000000000000
Rep_RE0000000400004
ZZTotal285833342455301457659601799
Total285933342457301489669601835[/p]
Again I hope this be helpful for you.
Best regards!
December 27, 2011 at 10:07 am
Thanks for responding.i tried with the change u gave , but same issue .....
Even if we dont put aggreate='SUM', it is defaulted to SUM.
The issue is the effectivedates are not coming in date order always, some times they are jumbled, for some date ranges they are coming in right order.
So, i need to know, if i can change this code with any other way , that the dates come in same order, with whatever range is given.
As the sp_transform is converting to datatype of varchar internally,i think this issue is happening.
Please advice, if i can get the resolution using some change to the code i gave above or any other approach
Thanks
December 27, 2011 at 12:54 pm
I have realized that you're doing a conversion from datetime to varchar as part of your process, altough in the [Results] table [EffectiveDate] is already a datetime field.
If [EffectiveDate] is of type varchar, you may use Convert(varchar(10), EffectiveDate, 102) instead of Convert(varchar(10), EffectiveDate, 101) and see the difference in the result. If it's datetime, then there is something really weird here, because the command SELECT DISTINCT EffectiveDate FROM Results which is used inside sp_TRANSFORM to get the columns headers, shows me the data in the correct order.
My apologies if this is not enough useful for you.
December 28, 2011 at 9:20 am
Thanks, i tried the change u asked me to use 101 instead of 12.
still the same issue.
i am pasting the final statement generated from sp_transform for display.
SELECT RepName, UserID,
SELECT @TransformPart = CASE WHEN @TransformPart IS NULL
THEN 'SUM(CASE CAST([EffectiveDate] AS VARCHAR(255)) WHEN ''' + CAST([EffectiveDate] AS NVarchar(255)) + ''' THEN MemberCount ELSE 0 END) AS ' +
QUOTENAME([EffectiveDate]) ELSE @TransformPart + ', SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN ''' + CAST([EffectiveDate]
AS nVARCHAR(255)) + ''' THEN MemberCount ELSE 0 END) AS ' + QUOTENAME([EffectiveDate]) END
FROM (SELECT DISTINCT [EffectiveDate] FROM #Results) SelInner
SUM(CASE CAST([EffectiveDate] AS VARCHAR(255)) WHEN 'Dec 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Dec 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Jun 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Jun 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Aug 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Aug 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'May 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [May 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Sep 1 2011 12:00AM' THEN MemberCount ELSE 0 END) AS [Sep 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Feb 1 2012 12:00AM' THEN MemberCount ELSE 0 END) AS [Feb 1 2012 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Nov 1 2011 12:00AM' THEN MemberCount ELSE 0 END) AS [Nov 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Jul 1 2011 12:00AM' THEN MemberCount ELSE 0 END) AS [Jul 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Oct 1 2011 12:00AM' THEN MemberCount ELSE 0 END) AS [Oct 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Jan 1 2012 12:00AM' THEN MemberCount ELSE 0 END) AS [Jan 1 2012 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Mar 1 2011 12:00AM' THEN MemberCount ELSE 0 END) AS [Mar 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Apr 1 2011 12:00AM' THEN MemberCount ELSE 0 END) AS [Apr 1 2011 12:00AM]
, SUM(MemberCount) As Total FROM #Results GROUP BY RepName, UserID ORDER BY RepName,
SUM(MemberCount) As Total FROM #Results GROUP BY RepName, UserID ORDER BY RepName
SELECT RepName, UserID, SUM(CASE CAST([EffectiveDate] AS VARCHAR(255))WHEN 'Dec 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Dec 1 2011 12:00AM],SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Jun 1 2011 12:00AM'
THEN MemberCount ELSE 0 END)AS [Jun 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Aug 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Aug 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'May 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [May 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Sep 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Sep 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Feb 1 2012 12:00AM'
THEN MemberCount ELSE 0 END) AS [Feb 1 2012 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Nov 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Nov 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Jul 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Jul 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Oct 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Oct 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Jan 1 2012 12:00AM'
THEN MemberCount ELSE 0 END) AS [Jan 1 2012 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Mar 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Mar 1 2011 12:00AM], SUM (CASE CAST([EffectiveDate] AS nVARCHAR(255)) WHEN 'Apr 1 2011 12:00AM'
THEN MemberCount ELSE 0 END) AS [Apr 1 2011 12:00AM], SUM(MemberCount) As Total FROM #Results GROUP BY RepName, UserID ORDER BY RepName
if you see, i seelcted 1/1/2011, and see 2012 dates are in middle instead of at the end.
December 28, 2011 at 9:51 am
I'm not able to get the same result that you get, but perhaps creating an index after the table [Results] be created, and before inserting records on it, may help us with this issue:
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Results]') AND name = N'IX_Results_EffectiveDate')
DROP INDEX [IX_Results_EffectiveDate] ON [dbo].[Results] WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX [IX_Results_EffectiveDate] ON [dbo].[Results]
(
[EffectiveDate] ASC
) ON [PRIMARY]
GO
This also works with temporary tables.
Maybe this isn't relevant, but if [EffectiveDate] field is of type DateTime, you can replace the CONVERT instructions with a direct assignment.
December 28, 2011 at 10:16 am
Hi,
DO we have any other alternate solution, other than using the sp_transform to get this results.
As , i want to use some other alternate way.
I tried the above index creation also, but no change in output table 🙁
Any help ............
thanks
December 28, 2011 at 11:13 am
In the following link you can find and download the source of sys_CrossTab sp:
http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/
You can leave it's name so, or use another that could be more useful for you.
After created the sp with the name [spCrossTab], I executed it in this way, and altough it's redundant in parameters, it worked fine:
exec spCrossTab @SQLSource = 'Results'
, @ColFieldID = 'EffectiveDate'
, @ColFieldName = 'EffectiveDate'
, @ColFieldOrder = 'EffectiveDate'
, @CalcFieldName = 'MemberCount'
, @RowFieldNames = 'RepName'
, @NumColOrdering = 1
, @CalcOperation = 'SUM'
, @RowTotals = 'Total'
I hope this will be really useful.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply