Creating simple looking, but tough to make report

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 2000 Forums

Transact-SQL (2000)

Creating simple looking, but tough to make report

Author

Topic

Sam_Canada
Starting Member

2 Posts

Posted - 2004-06-17 : 21:57:19

Hi folks

,

I have 3 tables - Table_A with Employees data, Table_B with Assignments data, and Table_C with corresponding Employee_IDs & Assignment_IDs. I have to write an SQL statement that will return this result set (Distinct List of Employees and their Assignments):

Empl_One Task1 Task2 Task3 Task4 ...etc.
Empl_Two Task1 Task2 Task3 NULL
Empl_Three Task1 NULL Task3 Task4
Empl_Four Task1 Task2 Task3 NULL
Empl_Five NULL Task2 NULL Task4 ...etc..
...etc...

Instead, I get this result

:

Empl_One Task1 NULL NULL
Empl_One Task1 Task2 NULL
Empl_One Task1 Task2 Task3
Empl_Two Task1 NULL NULL
Empl_Two Task1 Task2 NULL
Empl_Two Task1 Task2 Task3 ...etc...

Please, help me solve this task, that seems so trivial and simple, but is far from that!
Cheers!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-17 : 22:20:06

some sample table would be great!!

maybe case statements??

Corey

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-17 : 23:46:15

Give us the create table statements, along with about three rows of data for each table. Then an example of what you want to achieve. Employees with assignments seems to be a popular topic this semester.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Sam_Canada
Starting Member

2 Posts

Posted - 2004-06-18 : 05:57:53

Hi guys,

Table Employees (Example):

Empl_ID Empl_Name
1 Empl_One
2 Empl_Two ...etc...

Table Assignments (Example):
Assign_ID Assign_Name
1 Task1
2 Task2 ...etc...

Table Empl_Assign (Example):
Empl_ID Assign_ID
1 1
1 2
2 1
2 3
3 1 ...etc...

This is my code:

SELECT A.Empl_Name, B.Assign_Name Task1, B1.Assign_Name Task2 --As many joins as you have assignments...
FROM Employees A
JOIN Empl_Assign AB
ON A.Empl_ID = AB.Empl_ID
LEFT JOIN Assignments B
ON AB.Assign_ID = B.Assign_ID
LEFT JOIN Empl_Assign AB1 on A.Empl_ID = AB1.Empl_ID
LEFT JOIN Assignments B1
ON AB1.Assign_ID = B1.Assign_ID and B1.Assign_ID <> B.Assign_ID
LEFT JOIN Empl_Assign AB2 on A.Empl_ID = AB2.Empl_ID
LEFT JOIN Assignments B2
ON AB2.Assign_ID = B2.Assign_ID and B2.Assign_ID <> B.Assign_ID and
B2.Assign_ID <> B1.Assign_ID --...ETC...

The results I get you can see in my first posted message above, and I need to have a simple list of Employees in the first column, and their assignments in the following columns (in the same row!).

Your help would be appreciated!!!

Thanks!

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-18 : 09:02:56

Wrap MAX() around each assignment and GROUP BY a.Empl_Name

SELECT A.Empl_Name
, MAX(B.Assign_Name) Task1
, MAX(B1.Assign_Name) Task2 --As many joins as you have assignments...
FROM Employees A
JOIN Empl_Assign AB
ON A.Empl_ID = AB.Empl_ID
LEFT JOIN Assignments B
ON AB.Assign_ID = B.Assign_ID
LEFT JOIN Empl_Assign AB1 on A.Empl_ID = AB1.Empl_ID
LEFT JOIN Assignments B1
ON AB1.Assign_ID = B1.Assign_ID and B1.Assign_ID <> B.Assign_ID
LEFT JOIN Empl_Assign AB2 on A.Empl_ID = AB2.Empl_ID
LEFT JOIN Assignments B2
ON AB2.Assign_ID = B2.Assign_ID and B2.Assign_ID <> B.Assign_ID and
B2.Assign_ID <> B1.Assign_ID --...ETC...

GROUP BY a.Empl_Name

Also search here for "crosstab" and/or "Dynamic Crosstab"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources