Also the Week numbers are dynamic, the store numbers are static. If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns. My anecdotal results are that running this query over a couple of thousand rows completed in less than one second, and I actually had 7 subqueries.
- I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type.
- If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.
- Also the Week numbers are dynamic, the store numbers are static.
- DolphinDB also has built-in support for pivoting and the sql looks much more intuitive and neat.
- It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
- I have read the stuff on MS pivot tables and I am still having problems getting this correct.
Convert Rows to columns using ‘Pivot’ in SQL Server
- I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type.
- DolphinDB also has built-in support for pivoting and the sql looks much more intuitive and neat.
- Also the Week numbers are dynamic, the store numbers are static.
- I have read the stuff on MS pivot tables and I am still having problems getting this correct.
I have read the stuff on MS pivot tables and I am still having problems getting this correct.
Convert Rows to columns using ‘Pivot’ in SQL Server
DolphinDB also has built-in support for pivoting and the sql https://traderoom.info/the-pivot-point/ looks much more intuitive and neat. It is as simple as specifying the key column (Store), pivoting column (Week), and the calculated metric (sum(xCount)). It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially. I have a temp table that is being created, we will say that column 1 is a Store number, and column 2 is a week number and lastly column 3 is a total of some type.