1

I have a query which works fine, but I am trying to create a dynamic pivot out of it to get a better end result table.

I found this on SO but I cant relate it to my issue. The multi-part identifier could not be bound

My working code is this:

DECLARE @RangeDate as date
set @RangeDate = (select distinct cd.weDate from CM_DATA cd where cd.year = 2015 and cd.week = 45)
set @RangeDate = DATEADD(WW, -7, @RangeDate)

DECLARE @SQL as VARCHAR(MAX)
DECLARE @Columns AS VARCHAR(MAX)

SELECT @Columns = 
        COALESCE(@Columns + ', ','') + QUOTENAME(YearWeek)
FROM
(
    SELECT DISTINCT YearWeek
    FROM CM_DATA
    where weDate >= @RangeDate
) AS B

SET @SQL = '
WITH PivotData AS
(
select cd.Country
     , cd.Chain
     , cd.YearWeek
     , left(sm.Planogram, 2) as planogram
     , cd.StoreNo
     , cd.UID
     , cd.ShortCode
     , lp.Family
     , lp.ColourShort
     , pr.type
     , cd.Volume
     , ul.WOSOR
from vw_V2_UsrVarLst ul
left join CM_DATA cd on cd.Country = ul.CountryCode and cd.Chain = ul.Chain
left join V2_StoreMaster sm on sm.CountryCode = ul.CountryCode and sm.Chain = ul.Chain and sm.StoreNo = cd.StoreNo and sm.StoreNm = cd.StoreNm and cd.YearWeek between sm.YYYYWW and sm.YYYYWWEND
left join tblProducts pr ON pr.[COUNTRY CODE] = ul.CountryCode and pr.SKU = cd.UID
left join V2_LanguagePack LP ON LP.ShortCode = cd.ShortCode AND lp.Lang = ul.UsrLang
where cd.Country = ul.CountryCode and cd.Chain = ul.Chain and planogram is not null and left(cd.UID, 10) in (select lv.UID from V2_live lv where lv.CountryCode = ul.CountryCode and lv.Chain = ul.Chain and cd.YearWeek between lv.YYYYWW and lv.YYYYWWEND) and cd.weDate >= ' + @RangeDate + ' and sm.Planogram != ''Z''
)

select cd.Country
     , cd.Chain
     , left(sm.Planogram, 2) as planogram
     , cd.StoreNo
     , cd.UID
     , cd.ShortCode
     , lp.Family
     , lp.ColourShort
     , pr.type
     , cd.Volume
     , ' + @Columns + '
     , ul.WOSOR
FROM PivotData
PIVOT
(
    SUM(Volume)
    FOR YearWeek
    IN(' + @Columns + ')
) AS PivotResult'

EXEC (@SQL)

Can anyone spot what up here

KR Martin

Community
  • 1
  • 1
Martin Lucas
  • 13
  • 2

1 Answers1

0

Try SELECT @SQL instead of your EXEC.

You probably must set the output to Text and use the query options (right click into the query window) to set the max length of text output to a higher value (maximum is 8192).

Than you can paste the result of your dynamic SQL into a new query window and execute this there. You should get a speaking error message and you should even jump to the right place with a double click...

Good luck!

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Ok, It looks like the resultant T-SQL is too long. When I used your method I got the part of query appear in the message tab with an error message where the 4th left join is truncated half way through. – Martin Lucas Nov 11 '15 at 11:56
  • This is exatly what I was talking about... I suppose you are working with SSMS. Just do a right click into the query window and chose options. There you'll find `Text` and there something with `max characters` which is set to 256 by default. Put a higher value (8192) into this and retry... – Shnugo Nov 11 '15 at 11:58
  • Thanks Shnugo, In the end I put the query into a #Temp table and then ran the pivot over that. That made the T-SQL smaller and able to run perfectly. I then deleted the #Temp table at the bottom of my code. – Martin Lucas Nov 11 '15 at 13:19
  • @MartinLucas, glad to read that you could solve your problem. If it was my answer which brought you to the solution it would be very kind of you to accept this answer (the check below the vote counter). You might vote this up too, but I think your own reputation must be at least 15 points. Thx! – Shnugo Nov 11 '15 at 15:58