SQL Server Dynamic PIVOT throwing error

Question

HERE i am giving my full SP code. now SP run but not returning desired result.

ALTER PROCEDURE [dbo].[USP_LoadBogeyData]
(
@TickerID VARCHAR(10),
@TabID INT,
@Earning VARCHAR(10),
@PrePost VARCHAR(5),
@Version VARCHAR(5)
)
AS
BEGIN
Declare @MaxPeriodOrder INT
DECLARE @columns VARCHAR(MAX),
@columnsAlias VARCHAR(MAX),
@alenzi VARCHAR(MAX)

SET @columns = N''
SET @columnsAlias = N''
SET @alenzi = N''
SET @MaxPeriodOrder = 0

DROP TABLE IF EXISTS #tmpCalender
DROP TABLE IF EXISTS #tmpPeriod
DROP TABLE IF EXISTS #tmpPeriodAll
DROP TABLE IF EXISTS #tmpBogey
DROP TABLE IF EXISTS #tmpHierarchy

SELECT DISTINCT
A.*,
Convert(
numeric,
case
when A.Period like '%1Q%' then
ltrim(rtrim(REPLACE(A.Period, '1Q', ''))) + cast(0 as varchar)
when A.Period like '%2Q%' then
ltrim(rtrim(REPLACE(A.Period, '2Q', ''))) + cast(1 as varchar)
when A.Period like '%1H%' then
ltrim(rtrim(REPLACE(A.Period, '1H', ''))) + cast(2 as varchar)
when A.Period like '%3Q%' then
ltrim(rtrim(REPLACE(A.Period, '3Q', ''))) + cast(3 as varchar)
when
(
A.Period like '%4Q%'
And A.Period not like '%53 Weeks%'
) then
ltrim(rtrim(REPLACE(REPLACE(A.Period, '4Q', ''), '53 Weeks', '')))

• cast(4 as varchar)
  when
  (
  A.Period like '%4Q%'
  And A.Period like '%53 Week%'
  ) then
  ltrim(rtrim(REPLACE(REPLACE(A.Period, '4Q', ''), '53 Weeks', '')))
• cast(5 as varchar)
  when A.Period like '%2H%' then
  ltrim(rtrim(REPLACE(A.Period, '2H', ''))) + cast(6 as varchar)
  when
  (
  A.Period like '%FY%'
  And A.Period not like '%53 Week%'
  ) then
  ltrim(rtrim((REPLACE(A.Period, 'FY', '')))) + cast(7 as varchar)
  when A.Period like '%FY 53 Week%' then
  ltrim(rtrim((REPLACE(A.Period, 'FY 53 Weeks', '')))) + cast(8 as varchar)
  when A.Period like '%Trans Period%' then
  ltrim(rtrim((REPLACE(A.Period, 'Trans Period', '')))) + cast(9 as varchar)
  end
  ) as PeriodOrder
  Into #tmpPeriod
  FROM tblCalenderDetail A
  INNER JOIN tblCalenderMaster d
  ON d.ID = A.MasterID
  WHERE d.TickerID = @TickerId
  AND A.IsDeleted <> 'Y'

SELECT @MaxPeriodOrder = MAX(ISNULL(PeriodOrder, 0)) + 1
FROM #tmpPeriod

SELECT *
INTO #tmpPeriodAll
FROM
(
SELECT TOP 100 PERCENT
a.ID,
a.MasterID,
a.Period,
CASE
WHEN a.Period like '%53 Weeks%' THEN
left(a.Period, 7) + IIF(a.PeriodOrder <= cd.PeriodOrder, 'A', 'E') + ' 53 Weeks'
Else
a.Period + IIF(a.PeriodOrder <= cd.PeriodOrder, 'A', 'E')
End AS NewPeriod,
a.IsDeleted,
a.PeriodOrder,
a.IsActual
FROM #tmpPeriod a
OUTER APPLY
(
SELECT ISNULL(
(
SELECT MAX(PeriodOrder) FROM #tmpPeriod WHERE IsActual = 'Y'
),
@MaxPeriodOrder
) AS PeriodOrder
) AS cd
ORDER BY a.PeriodOrder
) X

SELECT @columns += N', ' + QUOTENAME([Name], '')                    
FROM                    
(                    
    SELECT TOP 100 PERCENT                    
        ISNULL(Period, '') AS [Name],                    
        PeriodOrder                    
    FROM #tmpPeriodAll                    
    ORDER BY PeriodOrder                    
) AS x1                    


SELECT @columnsAlias += N', ' + QUOTENAME([Name], '') + ' AS ' + QUOTENAME(NewPeriod)                    
FROM                    
(                    
    SELECT TOP 100 PERCENT                    
        ISNULL(Period, '') AS [Name],                    
        NewPeriod,                    
        PeriodOrder                    
    FROM #tmpPeriodAll                    
    ORDER BY PeriodOrder                    
) AS x2        

--print @columnsAlias
--return

IF EXISTS
(
SELECT *
FROM tblBBViewBogey
WHERE TickerID = @TickerID
AND SectionID = @TabID

)  

BEGIN

        ;WITH Hierarchy                    
         as (SELECT   

TickerID,
Type,
DisplayName,
ID,
ParentID,
SectionID,
LineItemID,
BMID,
FontName,
FontStyle,
fontSize,
UnderLine,
StrikeThrough,
FGColor,
BGColor,
Indent,
Box,
HeadingSubheading,
CurrencySign,
CurrencyCode,
AllowedDecimalPlace,
AllowPercentageSign,
AllowComma,
LineItemComments,
ColOrder,
RowNumber,
hierarchy = FORMAT(ID, '0000'),
level = 0
FROM tblBBViewBogey
WHERE ISNULL(ParentID, 0) = 0
AND TickerID = @TickerID
AND SectionID=@TabID

             UNION ALL                    
             SELECT                  

e.TickerID,
e.Type,
e.DisplayName,
e.ID,
e.ParentID,
e.SectionID,
e.LineItemID,
e.BMID,
e.FontName,
e.FontStyle,
e.fontSize,
e.UnderLine,
e.StrikeThrough,
e.FGColor,
e.BGColor,
e.Indent,
e.Box,
e.HeadingSubheading,
e.CurrencySign,
e.CurrencyCode,
e.AllowedDecimalPlace,
e.AllowPercentageSign,
e.AllowComma,
e.LineItemComments,
e.ColOrder,
e.RowNumber,
hierarchy = d.hierarchy + '.' + FORMAT(e.ID, '0000'),
level = level + 1
FROM tblBBViewBogey e
JOIN Hierarchy d
on e.ParentID = d.ID
WHERE e.TickerID = @TickerID
AND e.SectionID=@TabID
)

Select * into #tmpHierarchy FROM
(
SELECT * FROM Hierarchy
) H

Select * into #tmpCalender FROM
(
SELECT cd.Period,cd.ID as PeriodID From tblCalenderMaster cm
LEFT JOIN tblCalenderDetail cd ON cd.MasterID=cm.ID and cm.TickerID=@TickerID
) Y
ORDER BY PeriodID

SELECT * INTO #tmpBogey FROM
(
SELECT bg.SectionID,bg.LineItemID,bg.BMID,S.Section ,L.LineItem ,
bl.BM_Element,cm.Period, bg.PeriodID,bg.PeriodValues,
bg.PeriodFormula,bg.PeriodComments,bg.RowNumber,bg.ColumnNumber
FROM tblTicker_Bogey bg
LEFT OUTER JOIN tblSectionTemplate S ON bg.SectionID=S.SectionID AND s.Action <> 'D'
LEFT OUTER JOIN TblLineItemTemplate L ON bg.LineItemID=L.LineItemID AND L.Action <> 'D'
LEFT OUTER JOIN tblBlueMetricsMaster bl ON bl.Code=bg.BMID AND bl.TickerID=@TickerID
INNER JOIN #tmpCalender cm ON cm.PeriodID=bg.PeriodID
WHERE bg.TickerID=@TickerID AND bg.SectionID=@TabID
) Z
SET @alenzi =N'SELECT
TickerID,Type,DisplayName,ID,ParentID,SectionID,LineItemID,BMID,Section,LineItem,BM_Element,
/PeriodValues,/PeriodFormula,LineItemComments,PeriodComments,FontName,FontStyle,fontSize,
UnderLine,StrikeThrough,FGColor,BGColor,Indent,Box,HeadingSubheading,CurrencySign,CurrencyCode,
AllowedDecimalPlace,AllowPercentageSign,AllowComma,ColOrder,hierarchy,level,RowNumber,ColumnNumber,'+ STUFF(@columnsAlias, 1, 2, '')+'
FROM
(
SELECT
H.TickerID,
H.Type,
H.DisplayName,
H.ID,
H.ParentID,
H.SectionID,
H.LineItemID,
H.BMID,
B.Section,
B.LineItem,
B.BM_Element,
B.Period [Name],
B.PeriodValues,
B.PeriodFormula,
H.LineItemComments,
B.PeriodComments,
H.FontName,
H.FontStyle,
H.fontSize,
H.UnderLine,
H.StrikeThrough,
H.FGColor,
H.BGColor,
H.Indent,
H.Box,
H.HeadingSubheading,
H.CurrencySign,
H.CurrencyCode,
H.AllowedDecimalPlace,
H.AllowPercentageSign,
H.AllowComma,
H.ColOrder,
H.hierarchy,
H.level,
H.RowNumber,
B.ColumnNumber
FROM #tmpHierarchy H
LEFT OUTER JOIN #tmpBogey B
ON /H.SectionID=B.SectionID AND H.LineItemID=B.LineItemID AND H.BMID=B.BMID/ H.RowNumber=B.RowNumber
) V
PIVOT (MAX(PeriodValues) FOR [Name] IN (' + STUFF(@columns, 1, 1, '') +')) P
ORDER BY ColOrder '

exec(@alenzi )

END
ELSE
BEGIN

print 'later'
END

DROP TABLE IF EXISTS #tmpCalender
DROP TABLE IF EXISTS #tmpPeriod
DROP TABLE IF EXISTS #tmpPeriodAll
DROP TABLE IF EXISTS #tmpBogey
DROP TABLE IF EXISTS #tmpHierarchy
END

in tblTicker_Bogey table i am storing quarter wise TickerID, sectionid, lineitemid and BMID & periodID wise data. here is a screen shot what kind of data is stored there.

in this table tblBBViewBogey i am storing data with parent child relation. there some example data are GROUP, BLANK, LINEITEM and BM. in this table also some data will have SectionID, LineitemID and BMID.

i am left joining two tables and try to show data in pivot format where quarter wise period value will be shown. now my query run but return wrong data

please tell me where and how to rectify the code as a result i should get period value quarter wise.

if possible please give me rectified version of the code. thanks
two screen shot links
https://ibb.co/phj41sh
https://ibb.co/TBbsp1J

Answer 1

Print out @alenzi and show the result here.

print @alenzi

Answer 2

This part is most likely incorrect:

ColumnNumber'+ STUFF(@columnsAlias, 1, 2, '')+'

What is in @columnsAlias? I think you probably want a space after columnNumber and then just @columnsAlias (no STUFF) (Assuming the @columnsAlias is something like col1 as Column1, col2 as Column2, etc.)

Answer 3

This line:,ColumnNumber,'+ STUFF(@columnsAlias, 1, 2, '')+'

change to:

... , ColumnNumber, '+ STUFF(@columnsAlias, 1, 1, '')+'