Multiple Rows in a table to display as single row

[sngapoonage]

Hi everyone,

Here is my situation: I have a table of users in my database with all the usual user information it it. I have another table which lists the rights that each user has. this table has 2 columns 'Userid','Functionid'. Each user may have multiple rows in this table, for example if an ID of a user in the main users table was 10, they may have some rows like this in the 2 column table:

USERID      FUNCTIONID
10          3
10          5
10          6

I need to find a select statement that can combine these rows into a single row which also includes the information from the users row in the user table

USERID   FIRSTNAME     LASTNAME    FUNCTION1   FUNCTION2   FUNCTION3
10       Some          User        3           5           6

Is it possible to do this? The number of function thats any given user will have is not set either, so they could have 1 or have 10 or more.

Thanks

[debasisdas]

Is it possible to do this?

Yes that is very much possible .

What is the code that you are working on to achieve that ?

[sngapoonage]

Well I don't really know thats my problem. At the moment I'm using

SELECT * FROM BusinessUser

But for each user I need to do something like

SELECT * FROM BusinessUser_Functions WHERE Id = @the_id_given_by_the_first_table

So that it returns firstly the user row: Id,Firstname,Lastname,Email,Address etc. and then the function rows (example): 1,3,5,7,9

But all of that in ONE row per user if you know what I mean?

Thanks

[debasisdas]

You need to use COUNT and GROUP BY to get the desired result.

[BitBlt]

I think what you are trying to accomplish is a PIVOT or CROSSTAB. If you are using SQL2008, you can go to Books-On-Line and investigate the syntax as it has been incorporated into T-SQL.
If you are using SQL2000 or SQL2005, look at this link: http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
Some clever fellow wrote this great SP to simulate pivot. It's a little complicated, but it is well documented so you should be able to follow the instructions for using it.

Hope this helps!

[sngapoonage]

Yeah thats a bit to difficult for me to follow, I might stick with using AJAX to grab the function when an individual user is selected thanks anyway. I might look into in the future when I have some time to sit down and read it.

Cheers

[urtrivedi]

this is just overview who you can get your result.

Your case is dynamic, so you may build following query in you code page using for loop and iterating through function master table.

SELECT a.USERID, b.username,
min(case when a.functionid=3 then a.functionid else null end) f1,
min(case when a.functionid=5 then a.functionid else null end) f2,
min(case when a.functionid=6 then a.functionid else null end) f3
from rights a inner join users b on a.userid=b.userid
group by a.userid,b.username