TSQL Pivot 3 Tables

Question

I have 3 tables

Users

ID, UserID, balance,  name,     email 
1    Test1      5.44    Jan     jan@mail.com
2    Test2      9.22    Ken     kenmail.com

Modules

ID,    ModuleID, Price, Active (bit)
1       JAlert     2        1
2       jFeed      1        1
3       jCross     4        1

Subs

ID,    ModuleID, UserID, Timeline   (all nvarchar, except ID)
1       jAlert    Test1   0A12
2       jAlert    Test2   B241
4       jfeed     Test2   011A

I need to make a list with all Users and for each user need to have as column Nam all Active ModuleID from Modules and in the row the timeline like

UserID Name Balance Jalert  jFeed   jCross
Test1   Jan   5.44    0A12  Null    Null
Test2   Ken   9.22    B241  011A    Null

I've understood I have to use Pivot to translate rows into column.. and googling around I found a way to create dynamically the column head:

SELECT 
    @cols = ISNULL(@cols + ',','') + 
            QUOTENAME(ModuleID) 
FROM 
    (SELECT TOP 20 ModuleID
     FROM modules 
     WHERE Active = '1' 
     ORDER BY SortOrder ASC ) AS Columns; 

but I have problems with the other query:

SET @sql = N'SELECT UID, ' + @cols + 
  ' FROM (select ID, uid,  timeline from subscriptions) a 
   PIVOT (count(id) for timeline IN (' + @cols + ')) AS p '

that returns all zeroes, but the parameter has no sense..

while this that seems more reasonable...

SET @sql = N'SELECT UID, ' + @cols + 
  ' FROM (select ID, uid,  timeline from subscriptions) a 
  PIVOT (count(id) for id IN (' + @cols + ')) AS p '

returns an error during conversion from nvarchar to int

Problem is that I do not need aggregation but seems pivot requires such feature..

Can you suggest a solution or a workaround?

Thanks

Answer 1

Construct your dynamic query like this:

'select * from 
  (select u.userid, u.name, u.balance, s.moduleid, s.timeline
   from users u
   join subs s on u.userid = s.userid)q
pivot(max(timeline) for moduleid in(' + @cols + '))p'

This statement will group you results by 3 columns u.userid, u.name, u.balance(principle of elimination). In columns you will get all active modules. In rows you will get aggregation of timeline for each user or null if there is no such module fot the user.