I have the following tables:

TeamMemberInformation which has the following fields (MemberId, Name) and contains the following rows

MemberId Row 0 Name 1 Email 2 Phone

I also have a second table (User) which contains the following fields(UserId, Name, Email, Phone, etc) and contains the following rows:

userId Name Email Phone 0 Eric e@mail.com 555-1221 1 Steve s@mail.com 555-1345 2 bob b@mail.com 555-2314 3 lisa l@mail.com 555-2222

Given these two tables, I would like to generate the following resultset for a given user

TeamMemberInformation:Row User: Name Eric Email e@mail.com Phone 555-1212

What I'm trying to do is bind some of the user table columns to the appropriate rows in the TeamMember Information table to generate the resultset above which I can then bind to a grid. Being a novice to sql, I can't seem to figure out how to do this. Maybe there is a better way to do this, I just don't know how. Basically, the columns become rows in the resultset. Any recommendations or thoughts? By the way, is there a name for doing this?

What database are you using? I believe it's called a crosstab query and you can do it fairly easily in Access.

I'm using MS SQL. I will do some googling on crosstab. Thanks

It's called a pivot table (rows become columns, columns become rows) if I'm not mistaken.

Is it possible with LLBLGen? I think not and only because there's no way of specifying a CASE statement.

Ian wrote:

Is it possible with LLBLGen? I think not and only because there's no way of specifying a CASE statement.

What is 'it' in your question? How to do a pivot table or how to specify a case statement?

'It' meant a pivot table.

erichar11 wrote:

I have the following tables:

TeamMemberInformation which has the following fields (MemberId, Name) and contains the following rows

MemberId Row 0 Name 1 Email 2 Phone

I also have a second table (User) which contains the following fields(UserId, Name, Email, Phone, etc) and contains the following rows:

userId Name Email Phone 0 Eric e@mail.com 555-1221 1 Steve s@mail.com 555-1345 2 bob b@mail.com 555-2314 3 lisa l@mail.com 555-2222

Given these two tables, I would like to generate the following resultset for a given user

TeamMemberInformation:Row User: Name Eric Email e@mail.com Phone 555-1212

What I'm trying to do is bind some of the user table columns to the appropriate rows in the TeamMember Information table to generate the resultset above which I can then bind to a grid. Being a novice to sql, I can't seem to figure out how to do this. Maybe there is a better way to do this, I just don't know how. Basically, the columns become rows in the resultset. Any recommendations or thoughts? By the way, is there a name for doing this?

Is this correct?

TeamMemberInformation:Row User: Name Eric Email e@mail.com Phone 555-1212

or should it be:

TeamMemberInformation:Row User: Name Eric Email e@mail.com Phone 555-1221

Note: Phone no

and then the second line would be

eamMemberInformation:Row User: Name Steve Email s@mail.com Phone 555-1345

Yes, I would like to know how to pivot a table as well. Right now, I'm doing a for loop on the rows and building a datatable. Is there an easier way of doing this?

superman wrote:

Yes, I would like to know how to pivot a table as well. Right now, I'm doing a for loop on the rows and building a datatable. Is there an easier way of doing this?

Only either manually, through a 3rd party control, or in the database, if your database supports OLAP queries. (though llblgen pro can't generate the queries for you).