[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Dynamic Pivot Columns

The attached code works...but I need to make the pivot part dynamic because the months will change in my Stored Procedure based on paramaters passed in for BegDate and EndDate.

This is the DYNAMIC I've been trying to get working...no go so far
It returns this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

on this sql
declare @sql varchar(4000)
declare @columns varchar(8000)  
set @columns = (select Mth from #temp group by Mth)
 
set @sql = 'SELECT * FROM  
    (SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src
     PIVOT
    (Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'
 
EXEC (@sql)
SELECT 	v.rep,
		Sum(v.[Gross Rev]) as GrossRev, 
		DATENAME(month, v.Funded) as Mth
Into #temp
FROM	CRMPROD_01.dbo.v_P_Reporting v
WHERE	v.Funded is not null 
		and	v.Status <> 'Closed' 
		and (YEAR(v.Funded) = YEAR(getdate())) 
		and	(MONTH(v.Funded) > MONTH(getdate())-3)
Group by rep, v.Funded
ORDER BY v.rep, DATENAME(month, v.Funded)

SELECT Rep,June,July  
FROM (  
SELECT Rep,Mth,Grossrev FROM #temp)P  
PIVOT   
(SUM(Grossrev) FOR Mth IN (June,July))As Pvt
order by rep

Open in new window

0
lrbrister
Asked:
lrbrister
  • 3
  • 2
1 Solution
 
cyberkiwiCommented:
declare @sql varchar(4000)
declare @columns varchar(8000)  

select @columns = coalesce(@columns+',','') + '[' + Mth + ']'
from #temp
group by Mth
 
set @sql = '
    SELECT [rep], ' + @columns + '
    FROM  
    (SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src
     PIVOT
    (Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'
 
EXEC (@sql)
0
 
lrbristerAuthor Commented:
cyberkiwi:
That's almost perfect...but it doesn't put the month in the correct ascending order.
In my attached code the resulting table is
 
rep          April       February       January    etc......
myrep     1000      246                    1458    etc...

SELECT  v.rep,  
                Sum(v.[Gross Rev]) as GrossRev,   
                DATENAME(month, v.Funded) as Mth  
Into #temp  
FROM    CRMPROD_01.dbo.v_P_Reporting v  
WHERE   v.Funded is not null   
                and     v.Status <> 'Closed'   
                and (YEAR(v.Funded) = YEAR(getdate()))   
                and     (MONTH(v.Funded) > MONTH(getdate())-8)  
Group by rep, v.Funded  
ORDER BY v.rep, DATENAME(month, v.Funded)  


--Dynamic SQl and Pivot 
declare @sql varchar(4000)
declare @columns varchar(8000)  

select @columns = coalesce(@columns+',','') + '[' + Mth + ']'
from #temp
group by Mth
 
set @sql = '
    SELECT [rep], ' + @columns + '
    FROM  
    (SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src
     PIVOT
    (Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'
 
EXEC (@sql)

drop table #temp

Open in new window

0
 
cyberkiwiCommented:
I suspect just changing the order by in the insert clause will work.  It is currently going in alphabetically.
But just to be safe...
SELECT  v.rep,  
                Sum(v.[Gross Rev]) as GrossRev,   
                DATENAME(month, v.Funded) as Mth  ,
                month(v.Funded) as MthI  
Into #temp  
FROM    CRMPROD_01.dbo.v_P_Reporting v  
WHERE   v.Funded is not null   
                and     v.Status <> 'Closed'   
                and (YEAR(v.Funded) = YEAR(getdate()))   
                and     (MONTH(v.Funded) > MONTH(getdate())-8)  
Group by rep, v.Funded  
ORDER BY v.rep, month(v.Funded)

--Dynamic SQl and Pivot 
declare @sql varchar(4000)
declare @columns varchar(8000)  

select @columns = coalesce(@columns+',','') + '[' + Mth + ']'
from #temp
group by Mth, MthI
order by MthI
 
set @sql = '
    SELECT [rep], ' + @columns + '
    FROM  
    (SELECT [rep], [GrossRev], [Mth] as Mth from #temp) src
     PIVOT
    (Sum ([GrossRev]) FOR Mth IN (' + @columns + ') ) AS pvt'
 
EXEC (@sql)

drop table #temp

Open in new window

0
 
lrbristerAuthor Commented:
cyberkiwi:
Perfect!  Remind me to buy you a sody-pop sometime!
0
 
lrbristerAuthor Commented:
Perfiect!  Please look for follow-up question
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now