Dynamic columns in result set

[sngapoonage]

Hi everyone,

I'm having a bit of trouble figuring out what to do here. Basically I have 3 tables shown below

User Table

ID    Firstname    Lastname     etc....
1      James         Hay
2     Someone       Else

Permissions Table

PermissionID    UserID
     1            1
     2            1
     2            2
     3            2

Permissions Table Decode

ID      Name        Description
1      Web Access    This user can access the website
2      Uploader      This user is able to upload
3      Book Reader   This user can read books!

Basically what I'm doing first is SELECT * On the permissions decode table and then outputting that into a table on my page. What I then want to do is to get a list of users and all the rows in the permissions table for each user. In the example above you can see that the user with the id 1 has two rows in the permissions table, so he is 'allowed' for both of those thing (what they are can be found in the decode table). So is there a way perhaps to get all the ids from the Permissions table and create a column for each one in my result set?

Here is the end product I am looking for ie my result set. When I say dynamic I mean that as more is added to the decode table, more columns will show up in the query. There won't be alot though, theres 7 at the moment and it's not expected to grow much more than that.

Id(userid) Firstname   Lastname   Web Access   Uploader    Book Reader
   1        James        Hay          1            1           0
   2       Someone      Else          0            1           1

Thanks for your help, also suggestions to me finding a better way are welcome.

[Tess James]

Declare @user table
(
id int,
name varchar(20)
)

declare @userpermission table
(
userid int,
permissionid int
)

declare @permission table
(
id int,
name varchar(20)
)

create table #userpermissiondetails
(
username varchar(20),
permissionname varchar(20)
)

declare @privileges varchar(max)
declare @sql varchar(max)

insert into @user values (1,'michael')
insert into @user values (2,'evelyn')

insert into @userpermission values (1,1)
insert into @userpermission values (1,2)
insert into @userpermission values (2,2)
insert into @userpermission values (2,3)

insert into @permission values (1,'web access')
insert into @permission values (2,'uploader')
insert into @permission values (3,'book reader')

insert into #userpermissiondetails (username, permissionname)
select u.name as username , p.name as permissionname from @userpermission up
inner join @user u
on up.userid = u.id
inner join @permission p
on up.permissionid = p.id

select @privileges = isnull(@privileges + ', ', '') +
quotename(name)
from
(select name from @permission ) as temp
order by name

select @sql = 'select username ,' + @privileges + char(10) +
'from (select username,permissionname
from #userpermissiondetails) as pivotquery
pivot (max(permissionname)
for permissionname in (' + @privileges + ')
) as pvt1'

execute(@sql)

drop table #userpermissiondetails

[sngapoonage]

Thanks thats great. Having a few issues though here is my current query

create table #userpermissiondetails 
(
	Firstname varchar(60),
	Lastname varchar(60),
	Email varchar(100),
	Outlet varchar(60),
	Platform varchar(50),
	permissionid int
)
declare @privileges varchar(max)
declare @sql varchar(max)

insert into #userpermissiondetails (Firstname,Lastname,Email,Outlet,Platform, permissionid)
select distinct u.Firstname,u.Lastname,u.Email,u.Outlet,u.Platform, up.Functionality AS 'permissionid' from BusinessUser_Functions as up
inner join BusinessUsers AS u
on up.Userid = u.id
inner join BusinessUser_FunctionDecode AS d
on up.Functionality = d.Functionid
where up.Functionality = 2 OR up.Functionality = 5 OR up.Functionality = 6

select @privileges = isnull(@privileges + ', ', '') +
quotename(Functionid)
from
(select Functionid from BusinessUser_FunctionDecode) as temp
order by Functionid

select @sql = 'select Firstname,Lastname,Email,Outlet,Platform,' + @privileges + char(10) + 
'from (select Firstname,Lastname,Email,Outlet,Platform,permissionid
from #userpermissiondetails) as pivotquery
pivot (max(permissionid)
for permissionid in (' + @privileges + ')
) as pvt1'

execute(@sql)

drop table #userpermissiondetails

The main problem I am having is that the userpermissionsdetails table only has rows with a Functionid of 2,5 or 6. I realise this is because I have added the where clause in but what I want to do is to select ALL the rows for that userid IF they have a 2,5 or 6 row.

Also, not an issue really because I can just check for null but if possible, can the result set return ones and zeros instead of the column name or null?

My current results look like this

Firstname  Lastname  Email    Outlet    Platform  1    2    3    4    5    6    7
me         persone   [e-mail]   here      print   NULL  2   NULL NULL NULL NULL NULL
me         perstwo   [e-mail]   here      print   NULL  2   NULL NULL NULL NULL NULL
me         persthr   [e-mail]   here      print   NULL NULL NULL NULL  5   NULL NULL

[sngapoonage]

Ok I managed to get it working using an IN statement on the WHERE clause. Is there a better way because every time I try that people say that IN is costly

where u.Id IN (SELECT Userid FROM BusinessUser_Functions WHERE Functionality = 2 OR Functionality = 5 OR Functionality = 6)

[Tess James]

Get the users with functionality 2/5/6 from BusinessUsers or BusinessUser_Functions table and insert those into a temp table.
Then use this temptable in the 'INSERT INTO #userpermissiondetails' query.