Make a SQL view showing showing data from 3 tables like crosstab

I'm using ASP.net membership system with SQL Server 2000 database. What I want to do is create a view (or stored proc if that's better) which will return a table with the following columns:

UserName  |  Role1  |  Role2  |  Role3  |  ....  | Role*N*

Where the columns (besides Username) are generated to include all the ASP.net Roles in the application.

I need this to be generic (i.e. when I add a new Role, I want the procedure/view to compensate and list that new Role as a new column). I want the values i开发者_如何学JAVAn the Role fields to be 1 (True) or 0 (false) if a user in in that role. I can make a table that has such columns using Dynamic SQL to create the variable number of columns, but I cannot seem to populate them.

How can I accomplish this?

Tables involved are shown below:

CREATE TABLE [dbo].[aspnet_Users](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [UserId] [uniqueidentifier] NOT NULL,
    [UserName] [nvarchar](256) NOT NULL,
    [LoweredUserName] [nvarchar](256) NOT NULL,
    [MobileAlias] [nvarchar](16) NULL,
    [IsAnonymous] [bit] NOT NULL,
    [LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED 
(
    [UserId] ASC
) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[aspnet_Roles](
    [ApplicationId] [uniqueidentifier] NOT NULL,
    [RoleId] [uniqueidentifier] NOT NULL,
    [RoleName] [nvarchar](256) NOT NULL,
    [LoweredRoleName] [nvarchar](256) NOT NULL,
    [Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED 
(
    [RoleId] ASC
) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[aspnet_UsersInRoles](
    [UserId] [uniqueidentifier] NOT NULL,
    [RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [UserId] ASC,
    [RoleId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

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 pivot.

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.

Another option is to write a sproc which dynamically generates a pivot 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.

A static pivot query to give you the output you want might look something like this:

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

There are two lines that your dynamic SQL generator would have to keep updated, they are marked with the UpdateMe comments.

I don't have MSSQL installed here, so I can't test this query, but it should be quite close. The pivot reference might be helpful.

Faking pivots in SQL Server 2000 using aggregates and case:

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