 <p>Views can't dynamically add columns based on the data in them. Neither can straight queries. Since you want it to automatically change based on the data in the table, this completely rules out views and queries using constructs like <code>pivot</code>.</p> <p>Pretty much your only choice, given the constraints you've given us, is to generate a temp table with enough columns (dynamically), and populate that. This is still problematic as a sproc can't create and return a temp table since the table would be dropped when the sproc exits.</p> <p>Another option is to write a sproc which dynamically generates a <code>pivot</code> query and executes that. This is going to be pretty messy though. Do new roles get added so often that this is an absolute requirement? That's the only thing that's making it so difficult.</p> <p>A static pivot query to give you the output you want might look something like this:</p> <pre><code>select UserName, [1] as Role1, [2] as Role2, [3] as Role3 -- UpdateMe from ( select u.UserName, r.RoleID IsUserInRole(u.UserName, r.RoleName) RoleFlag from aspnet_users u cross join aspnet_roles r) source pivot ( RoleFlag for RoleID in ([1],[2],[3]) -- UpdateMe ) PivotTable </code></pre> <p>There are two lines that your dynamic SQL generator would have to keep updated, they are marked with the <code>UpdateMe</code> comments.</p> <p>I don't have MSSQL installed here, so I can't test this query, but it should be quite close. The <a href="http://msdn.microsoft.com/en-us/library/ms177410.aspx" rel="nofollow"><code>pivot</code></a> reference might be helpful.</p> <p>Faking pivots in SQL Server 2000 using aggregates and <code>case</code>:</p> <pre><code>select UserName, max(case when src.RoleID = 1 then RoleFlag else 0 end) Role1, --Repeat N times ... from ( select u.UserName, r.RoleID IsUserInRole(u.UserName, r.RoleName) RoleFlag from aspnet_users u cross join aspnet_roles r) src group by UserName </code></pre>