Single table to multiple table view

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

General SQL Server Forums

New to SQL Server Programming

Single table to multiple table view

Author

Topic

borthyn
Starting Member

4 Posts

Posted - 2012-07-13 : 06:08:58

Hi,

I wonder if anyone can help me with this problem. I am not quite sure how to phrase the question so excuse me if this a bit long winded.
I have 3 tables, User, Group and Department and a user can belong to multiple groups and to multiple departments.
Users Group Department
ID ID ID
User_Name Group_Name Dept_Name
User_ID User ID

Within these tables I have the following data.
Users
ID 1
User_Name John Smith

Group
ID 1 2 3
Group_Name Group1 Group2 Group3
User_ID 1 1 1

Department
ID 1 2 3
Dept_Name Dept1 Dept2 Dept3
User ID 1 1 1

I have created the following view to bring back all of the users, the Groups that they belong to and the Departments that they belong to.

SELECT U.User_Name, G.Group_Name, D.Dept_Name
FROM Users As U
INNER JOIN Group As G ON
U.ID = G.User_ID
INNER JOIN Department As D ON
U.ID = D.User_ID

This returns this set of data

User_Name Group_Name Dept_Name
John Smith Group1 Dept1
John Smith Group2 Dept1
John Smith Group3 Dept1
John Smith Group1 Dept2
John Smith Group2 Dept2
John Smith Group3 Dept2
John Smith Group1 Dept3
John Smith Group2 Dept3
John Smith Group3 Dept3

Is it possible to write a view on the above tables (I am not the dba and would not be allowed to amend the table structure in anyway) so that it would bring back the following data set?

User_Name Group_Name Dept_Name
John Smith Group1 Dept1
John Smith Group2 Dept2
John Smith Group3 Dept3

Thanks,
Borthyn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-13 : 10:04:40

[code]
SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

borthyn
Starting Member

4 Posts

Posted - 2012-07-17 : 09:49:37

Thank you very much for taking the time to respond but I am getting the following error.
I am doing the SQL in Microsoft SQL Server 2005.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.

Cheers
Andy

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-17 : 09:55:01

SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 10:00:45

quote:
Originally posted by borthyn

Thank you very much for taking the time to respond but I am getting the following error.
I am doing the SQL in Microsoft SQL Server 2005.

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'AS'.

Cheers
Andy

missed braces


SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

borthyn
Starting Member

4 Posts

Posted - 2012-07-17 : 10:05:32

quote:
Originally posted by jleitao

SELECT User_Name,Group_Name,Dept_Name
FROM Users u
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Group)g
ON g.User_ID = u.User_ID
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY User_ID ORDER BY (SELECT 1)) AS Seq,*
FROM Department)d
ON d.User_ID = u.User_ID
AND d.Seq = g.Seq

Wow - that is brilliant. Thanks you very much indead. I really do appreciate you taking the time to help.

Regards,
Andy

borthyn
Starting Member

4 Posts

Posted - 2012-07-18 : 05:48:28

Just a quick note to say that I have implemented the SQL that you supplied me into a report today and my boss is very very pleased with the results.

Whist allowing some of the credit to reflect onto me, I did tell him that two selfless people had helped me out on this forum.

So visakh16 and jleitao thank you once again.
Andy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 09:46:49

quote:
Originally posted by borthyn

Just a quick note to say that I have implemented the SQL that you supplied me into a report today and my boss is very very pleased with the results.

Whist allowing some of the credit to reflect onto me, I did tell him that two selfless people had helped me out on this forum.

So visakh16 and jleitao thank you once again.
Andy

no problem...you're welcome

glad that we could help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources