How to dynamically rotate a date as a column

Question

I have a table with an identifier and product names and another table with a stock of these products on certain dates. For example, Item1 had a stock of 6 in stock on 1-1-2014 and 8 on 2-1-2014 . I am trying to show them in a stored procedure so that it looks like a calendar showing all the dates in a month and the stock available in the cells. What is the best way to show this?

For instance:

 Name | 1-1-2014 | 2-1-2014 | 3-1-2014 | 4-1-2014 Item1 | 6 | 8 | | 6 Item2 | | 2 | 1 | 

Source Tables - Names

  ID | Name 1 | Item1 2 | Item2 

Original Tables - Stocks

  ID | NameID | Stock | Date 1 | 1 | 8 | 2-1-2014 2 | 2 | 2 | 4-1-2014 

Answer 1

Here is an example table

 SELECT * INTO #Names FROM ( SELECT 1 ID,'ITEM1' NAME UNION ALL SELECT 2 ID,'ITEM2' NAME )TAB SELECT * INTO #Stockdates FROM ( SELECT 1 ID,1 NAMEID,8 STOCK,'2-1-2014 ' [DATE] UNION ALL SELECT 2 ID,2 NAMEID,2 STOCK,'4-1-2014 ' [DATE] )TAB 

Put the connection data in a dark table

 SELECT N.NAME,S.[DATE],S.STOCK INTO #TABLE FROM #NAMES N JOIN #Stockdates S ON N.ID=S.NAMEID 

Get columns for pivot

 DECLARE @cols NVARCHAR (MAX) SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']', '[' + CONVERT(NVARCHAR, [DATE], 106) + ']') FROM (SELECT DISTINCT [DATE] FROM #TABLE) PV ORDER BY [DATE] 

Now turn it

 DECLARE @query NVARCHAR(MAX) SET @query = ' SELECT * FROM ( SELECT * FROM #TABLE ) x PIVOT ( SUM(STOCK) FOR [DATE] IN (' + @cols + ') ) p ' EXEC SP_EXECUTESQL @query 

And your result is here

Answer 2

Step 1 - Fill in the blanks (maybe not necessary)

If your stock table does not contain stocks from each day for each product, then you should get all the dates in a month from another place. You can generate them using recursive CTE: (variable declarations omitted)

 with dates as ( select @startdate as [date] union ALL select [date] + 1 from dates where [date] < @enddate ) select @strDays = COALESCE(@strDays + ', ['+ convert(varchar(8), [date],112) + ']', '['+ convert(varchar(8), [date],112) + ']') from dates; 

You can use your preferred date format, but it’s important to keep it in all queries.

Step 2 - Bring the data into normal form. You can save it in a temporary table or use CTE again and combine this step with step 3.

Attach dates (top) using products (full) and stock (left) to get the table as follows:

 date product_id items 

For products and dates when stock is not available, you show 0. isnull will do the trick. Make sure the date column is converted to varchar in the same format as in the CTE above.

Step 3 - rotate the table (obtained in step 2) with the date column in the dynamic query.

I can give you more details, but not now. You can see something like this in another answer: Distribute different values ​​across different columns