0

I am trying to manipulate R12 sales info so that I have a summary of qty shipped to each ship to ID. I need these distinct ship to's in columns in a report. Is there a way to use the column headers from the pivot table? If I first create ##TempTableTesting, I don't know how to rename the columns with the ship to IDs. But if I don't first create the table, it says invalid object later on. What is the best way around this?

IF OBJECT_ID('tempdb..#R12') IS NOT NULL
    DROP TABLE #R12

SELECT #t.inv_mast_uid,
       #t.ship_to_id,
       SUM(#t.qty_shipped) as qty_shipped
INTO #R12
FROM #t
WHERE #t.invoice_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, 0))-12, 0) AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) 
GROUP BY #t.inv_mast_uid,
       #t.ship_to_id

IF OBJECT_ID('TEMPDB.dbo.##TempTableTesting') IS NOT NULL 
    DROP TABLE ##TempTableTesting

DECLARE @Columns NVARCHAR(MAX)
SELECT @Columns = COALESCE(@Columns + ',','') + QUOTENAME(ship_to_id) FROM (SELECT DISTINCT #R12.ship_to_id FROM #R12) shiptos

DECLARE @sql NVARCHAR(MAX)
SET @sql = 'SELECT inv_mast_uid, ' + @Columns + '
into ##TempTableTesting
FROM #R12
PIVOT(SUM(qty_shipped) 
FOR ship_to_id IN (' + @Columns + ')) AS PVTTable'

execute sp_executesql @sql
Amanda Mulryan
  • 1
  • visit https://stackoverflow.com/questions/62166553/pivot-query-multiple-joining – Praful Bangal Jun 03 '20 at 15:03
  • I don't think that is exactly what I'm trying to do. I just need to be able to call that table later on in my report. – Amanda Mulryan Jun 03 '20 at 16:13
  • What do you mean by "later on"? If it is later in the same SP, then it should work, if it executed from outside SP in a different session, then the table will be dropped. Have a look at this one: https://stackoverflow.com/questions/22503366/dynamic-pivot-results-to-a-temp-table – Alex Jun 04 '20 at 01:50

0 Answers0