How to use SQL Server 2005 Pivot based on lookup table -

How to use SQL Server 2005 Pivot based on lookup table -

table [status] has next data:

id status 1 paymentpending 2 pending 3 paid 4 cancelled 5 error

====================================

data table has next structure:

id weeknumber statusid 1 1 1 2 1 2 3 1 3 4 2 1 5 2 2 6 2 2 7 2 3

looking pivot

week # paymentpending pending paid cancelled week 1 1 1 1 0 week 2 1 2 1 0

a pivot might this:

select * (select 'week ' + cast(d.weeknumber varchar(2)) [week #], s.status datatbl d inner bring together status s on d.statusid = s.id ) derived pivot ( count(status) status in ([paymentpending], [pending], [paid], [cancelled]) -- add together [error] if needed ) pvt

if expect number of items in thestatustable alter might want consider using dynamic pivot generate column headings. this:

declare @sql nvarchar(max) declare @cols nvarchar(max) select @cols = isnull(@cols + ',','') + quotename(status) (select id, status status) statuses order id set @sql = n'select * (select ''week '' + cast(d.weeknumber varchar(2)) [week #], s.status datatbl d inner bring together status s on d.statusid = s.id) q pivot ( count(status) status in (' + @cols + ') ) pvt' exec sp_executesql @sql;

sample sql fiddle

sql sql-server sql-server-2005 pivot