0

I'm trying to create a crosstab/pivot which takes dynamic dates and gives me the corresponding sum values for a given user at each of the respective dates.

I'm only just starting, but not sure where to go next - perhaps someone could nudge in the right direction :-)

/* create temp table */
    CREATE TABLE #tmpDates
    ( tDate date )

    INSERT into #tmpDates
    VALUES
        ('2022-11-09'),
        ('2022-11-08'),
        ('2022-11-07')

pivot (so far)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(tDate) FROM (select distinct tDate from #tmpDates) as tmp

set @query = 
N'
    SELECT * from 
    (
                SELECT 
                UID, 
                SUM(myValue) as sumValue
            FROM dbo.tblTrans
            WHERE myValue > 0 AND tDate <= ' + @cols + '
            GROUP BY UID
            ) src
    pivot 
    (
        max(myValue) for cast(tDate as date) in (' + @cols + ')
    ) piv
'

execute(@query)

The output i'm looking for is something like

UID    2022-11-09    2022-11-08    2022-11-07
12     420           400           350
15     100           50            0
BigIWT
  • 243
  • 1
  • 5
  • 14
  • Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – Thom A Nov 09 '22 at 11:41
  • Thanks - but not exactly , as the example provided has it's data in the same table. For my situation, we're dealing with a temp table containing the required dates, and another table which contains the transactions data itself. – BigIWT Nov 09 '22 at 12:03
  • That doesn't change the answer. Use the temporary table (`#tmpDates`) to create your dynamic columns, and then you can `PIVOT` on your table `(dbo.)tblTrans`. – Thom A Nov 09 '22 at 12:05
  • I've updated the original sample to what I think it should be, but now getting an error : Incorrect syntax near '2022-11-02'. – BigIWT Nov 09 '22 at 12:27
  • `tDate <= ' + @cols + '` is *not* going to work. What do you think `tDate <= [2022-11-09], [2022-11-08], [2022-11-07]` is going to do? You need to parametrise that statement, or perhaps you should be `JOIN`ing to your temporary table instead. – Thom A Nov 09 '22 at 12:28
  • thanks for the guidance, i'll work around it – BigIWT Nov 09 '22 at 12:51

0 Answers0