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

The table [status] has the following data:

  id position 1 payment payment 2 pending 3 Payment 4 cancellation 5 error  

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

The data structure has the following structure:

  id week number status ID 1 1 2 3 2 3 1 3 4 2 1 5 2 2 2 2 2 2 7 2 3  

Search for a pivot

  weeks # payment payments pending cancelation canceled 1 1 1 1 0 weeks 2 1 2 1 0  

A pivot can look like this :

  SELECT * FROM (SELECT 'Week' + CST (D. W.C.number A.S. Verma (2)) [WEEK #], S. Status FRI Datatable D Inner Join S.S. D. Status ID = S.ID) in case of receiving (for position) (PEVOT) ([payment payment], [pending], [paid], [canceled]) - [error] if If necessary, add to [Private]  

If you expect to change the number of items in the status table, Titles to generate might want to consider using a dynamic axis. Something like this:

  announcement @ sql AS nVARCHAR (maximum) DECLARE @ cols as nVARCHAR (max) SELECT @cols = ISNULL (@cols + ',', '') + bid (position) From the order set by the order @ sql = N'SELECT * for situations (from select ID, position to position), (select "Week") + Cast (D. W.C.number AS Varna (2)) Status Status DD StatusID = SID from S status status (D + StatusID = SID), Q PIVOT (Status + '+ cols +') for status (condition) Private Axle S P_X acutesclose@sql;