0

I have the following CrossTab SQL Query & its works fine. However, I have limitation on hard-coding 100 Store No, Is it possible that I can bring StoreNo to be generated dynamically in case if a new StoreNo is inserted.

below is my query:

SELECT

T1.[Menu_Code] AS 'Menu Code',
T1.[Short_Menu_Name] AS 'Menu Name',
SUM(case when T2.Store_id=1 then  (T2.PMXUSO + T2.PMXUSG+ T2.PMXUSI+  T2.PMXUSD+  T2.PMXUPR+  T2.PMXUEM+  T2.PMXUMM) else 0 end) AS 'Store No: 1 | Units Sold',
SUM(case when T2.Store_id=1 then ( ( (T2.PMXUSO + T2.PMXUSI+ T2.PMXUSG+ T2.PMXUSD+ T2.PMXUPR+ T2.PMXUEM+ T2.PMXUMM) * (T2.PMXPOB ))+(T2.PMXUSG * T2.PMXPGN) ) else 0 end) AS 'Store No: 1 | Units Sale',

SUM(case when T2.Store_id=2 then  (T2.PMXUSO + T2.PMXUSG+ T2.PMXUSI+  T2.PMXUSD+  T2.PMXUPR+  T2.PMXUEM+  T2.PMXUMM) else 0 end) AS 'Store No: 2 | Units Sold',
SUM(case when T2.Store_id=2 then ( ( (T2.PMXUSO + T2.PMXUSI+ T2.PMXUSG+ T2.PMXUSD+ T2.PMXUPR+ T2.PMXUEM+ T2.PMXUMM) * (T2.PMXPOB ))+(T2.PMXUSG * T2.PMXPGN) ) else 0 end) AS 'Store No: 2 | Units Sale'

FROM  [Master_Menu_Items] T1 LEFT JOIN [Fact_Qtlysales_CSHPMXP0] T2 ON T1.Menu_code = T2.PMXMIC

WHERE T2.FCT_DATE_KEY=20160710 and T2.Store_id IN (1,2)

GROUP BY T1.[Menu_Code],
         T1.[Short_Menu_Name]

ORDER BY T1.[Menu_Code]

Above Query shows two StoreID & I have around 100 which I need to bring them dynamically.

Omran Moh'd
  • 87
  • 1
  • 11
  • SQL Server does not have a fully dynamic pivot function - you basically have to query to get the columns and build up a SQL statement dynamically. – D Stanley Jul 12 '16 at 20:52
  • 1
    @DStanley Is it possible if you can help me further more in converting the above? – Omran Moh'd Jul 12 '16 at 20:58
  • If you want to use a crosstab instead of dynamic pivot you can read this article on the topic. http://www.sqlservercentral.com/articles/Crosstab/65048/ I also just posted an answer yesterday using a dynamic crosstab. http://stackoverflow.com/questions/38313434/replacing-alphabets-to-zero-in-any-given-string-in-sql/38315386#38315386 – Sean Lange Jul 12 '16 at 21:02
  • @SeanLange Thanks for the info shared here. and I know am asking a lot, would you plz help me in framing the query & what all changes are required.. – Omran Moh'd Jul 12 '16 at 21:11
  • All your aggregate columns need to be part of the dynamic portion of a dynamic cross tab. I couldn't begin to start writing it for you because I have nothing to work with. If you read the article about dynamic cross tabs and actually understand it then you will know what need to change. I am willing to help nudge you but I am not willing to do it for you. – Sean Lange Jul 12 '16 at 21:38

0 Answers0