Dynamic pivot

Transact-SQL
1

I've the following table.
INSERT INTO [dbo].[Mrp]
([StockCode]
,[FDate]
,[Line]
,[Qty]
,[Description]

 VALUES
       (<StockCode, varchar(30),>
       ,<FDate, datetime,>
       ,<Line, decimal(3,0),>
       ,<Qty, decimal(18,6),>
       ,<Description, varchar(50),>

With the following data

StockCode FDate Line ForecastQtyOutst Description
12 04-Jun-18 1 739 tech2
12 25-Jun-18 3 200 tech2
14 02-Jul-18 1 403 tech4
12 09-Jul-18 3 250 tech2
11 06-Aug-18 1 400 tech1
11 13-Aug-18 3 24 tech1
10 03-Sep-18 1 226 tech10

I am trying to achieve the following results. Date Field will be dynamic as it changed monthly, Any help greatly appreciated. Thank You

image
image.png782×227 3.58 KB

2

DECLARE @DateList varchar(max),@SQL varchar(max)
SELECT DISTINCT FDate INTO ##TMP FROM Mrp ORDER BY FDate
SET @DateList = STUFF((SELECT ',[' + CONVERT(NVARCHAR( 50),FDate) + ']' FROM ##TMP FOR XML PATH('')),1,1,'')
SELECT @DateList
SET @SQL= 'SELECT *
FROM
(
SELECT FDate , Qty FROM Mrp
)m
PIVOT(SUM(Qty) FOR FDate IN (' + @DateList + '))n'

EXEC(@SQL)

DROP TABLE ##TMP

3

declare @sql nvarchar(2048)='
select StockCode

,sum(['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-1,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']) as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-1,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']
,sum(['+datename(month,dateadd(month,datediff(month,0,current_timestamp),0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']) as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp),0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']
,sum(['+datename(month,dateadd(month,datediff(month,0,current_timestamp)+1,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']) as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)+1,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']
,sum(['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-+2,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']) as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-+2,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']
from(
select StockCode,dense_rank() over(order by StockCode) as RowNumber
,case
when FDate>=dateadd(month,datediff(month,0,current_timestamp)-1,0)
and FDate<dateadd(month,datediff(month,0,current_timestamp),0)
then sum(Qty)
else 0
end
as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-1,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']

	, case when FDate>=dateadd(month,datediff(month,0,current_timestamp),0)
           and FDate<dateadd(month,datediff(month,0,current_timestamp)+1,0)
          then sum(Qty)
          else 0
       end
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp),0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']
	   
	, case when FDate>=dateadd(month,datediff(month,0,current_timestamp)+1,0)
           and FDate<dateadd(month,datediff(month,0,current_timestamp)+2,0)
          then sum(Qty)
          else 0
       end
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)+1,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']
	   
	,case when FDate>=dateadd(month,datediff(month,0,current_timestamp)+2,0)
           and FDate<dateadd(month,datediff(month,0,current_timestamp)+3,0)
          then sum(Qty)
          else 0
       end
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-+2,0))+ '-'+ right(cast(year(getdate()) as varchar),2)+']
	   from [dbo].[Mrp]

group by StockCode,FDate)t
group by StockCode , RowNumber'

execute sp_executesql @sql;

4

Xml path() works from sql server 2014 version

drop table #test
create table #test

   (StockCode varchar(30),
   FDate datetime,
   Line decimal(3,0),
   Qty decimal(18,6),
   Description varchar(50)
   )

insert #test values ('12', '04-Jun-18',1,739,'tech2')
insert #test values ('12', '25-Jun-18',3,200,'tech2')
insert #test values ('14', '02-Jul-18',1,403,'tech4')
insert #test values ('12', '09-Jul-18',3,250,'tech2')
insert #test values ('11', '06-Aug-18',1,400,'tech1')
insert #test values ('11', '13-Aug-18',3,24,'tech1')
insert #test values ('10', '03-Sep-18',1,226,'tech10')

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + DateName(month, FDate)
FROM #test c
FOR XML PATH(''))
--, TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,' ')

set @query = 'SELECT StockCode, ' + @cols + ' from
(
select StockCode
,DateName(month, FDate) Monthname
,Qty
from #test
) x
pivot
(
sum(Qty)
for Monthname in (' +@cols + ')
) p '

execute(@query)

5

this works fine however when I am trying to save the view i get the following message:

INCORRECT SYNTAX NEAR DECLARE? how can I save outcome from this procedure into a view or table?

I need to save this view for reporting.

Thanks

6

This puts it into a Global temp table called ##Results

drop table if exists #test
drop table if exists ##Results
create table #test

   (StockCode varchar(30),
   FDate datetime,
   Line decimal(3,0),
   Qty decimal(18,6),
   Description varchar(50)
   )
insert #test values ('12', '04-Jun-18',1,739,'tech2')
insert #test values ('12', '25-Jun-18',3,200,'tech2')
insert #test values ('14', '02-Jul-18',1,403,'tech4')
insert #test values ('12', '09-Jul-18',3,250,'tech2')
insert #test values ('11', '06-Aug-18',1,400,'tech1')
insert #test values ('11', '13-Aug-18',3,24,'tech1')
insert #test values ('10', '03-Sep-18',1,226,'tech10')

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + DateName(month, FDate)
FROM #test c
FOR XML PATH(''))
--, TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,' ')

set @query = 'SELECT StockCode, ' + @cols + ' into ##Results from
(
select StockCode
,DateName(month, FDate) Monthname
,Qty

from #test
) x
pivot
(
sum(Qty)
for Monthname in (' +@cols + ')
) p '

exec sp_executesql @query

select * from ##Results
drop table ##Results
7

Thank You, the above store procedure executed OK however I can't find the results anywhere in Database?
Can the results be copied to a regular View so I can use it for Reporting?

Thanks