RSA Scripts

Dynamic Pivot

Dynamic Pivot.

  1. Sql Server
  2. Pivot & UnPivot
  3. Intermediate
  4. Vaknin Sammy

Last updated:

Warning: Review and test in a non-production environment before running.

Back to results

select * 
  into #t
from openquery([LinkedServer],'select memberid,calcdate,redemptionsum 
from SourceDb.dbo.ResultTable r
inner join MemberDb.dbo.member m on m.recordkey = r.memberkey
order by memberid,calcdate')

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(t.calcdate) 
            FROM #t t
            order by ','+ QUOTENAME(t.calcdate)
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

set @query = 'SELECT memberid, ' + @cols + ' from 
            (
                select * from #t
           ) x
            pivot 
            (
                 max(redemptionsum)
                for calcdate in (' + @cols + ')
            ) p '

execute(@query)

drop table #t;