Inner join displaying data that appears multiple time in query

[DaveRook]

Not sure if this is for SQL or .net! I have 2 tables which looks like

table1
ID Name
1 Jon

and

table2
ID Number Job
1 1 IT
2 1 Web
3 1 Admin


An inner join makes one table with 3 rows (I excluding the ID from table 2 for the join) which is saved as a view

viewTable
ID Name Number Job
1 Jon 1 IT
2 Jon 1 Web
3 Jon 1 Admin

I want to display these results on a web page via the grid view. However, I don't want to show the user 3 times, but I do want to show each job. ideally:

ID Name Number Job Job Job
1 Jon 1 IT Web Admin

(I know the above won't work because of the duplicated column titles - it is for visual aid only)

What is the best way to do this? Is there a fault with the database design already?

Dave

[ck9663]

You can do that in your front-end, too. In T-SQL, you have to PIVOT/UNPIVOT your result set to do what you want.

Good Luck!!!

~~ CK

[DaveRook]

Perfect. Thank you.

FYI for any one else who finds this post

http://msdn.microsoft.com/en-us/library/ms177410.aspx