many to many relationship / cross tabs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

many to many relationship / cross tabs

Author

Topic

mrdimn
Starting Member

2 Posts

Posted - 2010-02-01 : 18:26:48

Hi Folks -

I am trying to do a many to many relationship and create a final result table that has the records from one table as one of the columns, the other columns will be from another table, and the actual data population should be from the table that actually joins the 2 other tables together.

Here's the example:


Table1: person
name_ID     Name:
1           Jones
2           Smith
3           Rogers


Table2: computers
comp_ID       Computer
1        PC - Desktop
2        Mac - Desktop
3        PC-Notebook
4        Mac-Notebook


Table3:  name_computer
Name_id   comp_id
1         1
1         3
2         1
2         2
2         3
2         4

What I am trying to do is create a final result table that will show all 3 names in one column, the values in Table 2 as the other columns
and finally use the data in the joining table to display an 'x' in the proper grid.


Name           PC-Desktop   Mac-Desktop    PC-Notebook   Mac-Notebook
Jones             x                             x
Smith             x             x               x             x
Rogers

I was thinking about cross tabs, but I don't need to summarize any data.

I am open to any ideas or suggestions.

Thanks.

Its actually short for Mr. Diamond.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-02-02 : 01:19:15

[code]
is this u want
declare @person table(name_ID int,Name varchar(32))
insert into @person select 1, 'Jones' union all select
2,'Smith' union all select
3,'Rogers'

declare @computers table(comp_ID int,Computer varchar(64))
insert into @computers select 1,'PC - Desktop' union all select
2,'Mac - Desktop' union all select
3,'PC-Notebook' union all select
4,'Mac-Notebook'

declare @name_computer table(Name_id int,comp_id int)
insert into @name_computer select
1,1 union all select
1,3 union all select
2,1 union all select
2,2 union all select
2,3 union all select
2,4

select name,[PC - Desktop],[Mac - Desktop],[PC-Notebook],[Mac-Notebook]
from (
select n.name_id,n.name,c.comp_id,c.computer
from @person n
LEFT join @name_computer nc on n.name_id = nc.name_id
LEFT join @computers c on nc.comp_id = c.comp_id)s
pivot (max(comp_id) for computer in ([PC - Desktop],[PC-Notebook],[Mac - Desktop],[Mac-Notebook]))p

select
name,
MAX(CASE WHEN Computer = 'PC - Desktop' THEN 'X' END) AS 'PC - Desktop',
MAX(CASE WHEN Computer = 'Mac - Desktop' THEN 'X' END) AS 'Mac - Desktop',
MAX(CASE WHEN Computer = 'PC-Notebook' THEN 'X' END) AS 'PC-Notebook',
MAX(CASE WHEN Computer = 'Mac-Notebook' THEN 'X' END) AS 'Mac-Notebook'
FROM (
select n.name_id,n.name,c.comp_id,c.computer
from @person n
LEFT join @name_computer nc on n.name_id = nc.name_id
LEFT join @computers c on nc.comp_id = c.comp_id)s
Group By name
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:25:53

if names of computers can vary use

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

mrdimn
Starting Member

2 Posts

Posted - 2010-02-02 : 11:58:19

Hi Folks -

Thanks for the suggestions and possible answers... I will have to try them out.

When it comes to the tables, the people can change as well as the computers.

It's a matter of trying to work in a truly dynamic situation.

For example, to make this even more interesting, In the person table, I could add in another field, let's say, "School level" with possible answers ranging from 1 - 12, and the computers table could have more answers in it, but only certain ones appear for certain grades (school level).

Smith, Jones, and Rogers were all in 1st grade.
In 2nd grade we have Martin, Franks, Rigby
and they deal with PC-Desktop and Linux-Desktop.

So I would only want to see those 3 people with only those 2 computers types listed.

It's crazy, I know, and I truly appreciate any ideas I can get.

Thanks.

Its actually short for Mr. Diamond.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources