Seperating data in SQL

You could create a query for each department and UNION them together, but you would need to update the query every time a new department is added. You may need to post your query for better advice:

SELECT * FROM Blah WHERE [DEPT] = Department1
UNION ALL
SELECT * FROM Blah WHERE [DEPT] = Department2
UNION ALL
SELECT * FROM Blah WHERE [DEPT] = Department3

Hi Russel,
Below is the query I use to create the view in SQL from AD. The query was much simpler before I plopped it into SQL, as SQL added a few things to it which make it look more complicated than it really is. The end result though is it grabs everyone I need

SELECT     TOP (100) PERCENT userAccountControl, mail, facsimileTelephoneNumber, postalCode, st, l, streetAddress, mobile, telephoneNumber, title, department,
                      displayName
FROM         OPENQUERY(ADSI,
                      'SELECT displayName, department, title, telephoneNumber, mobile, streetAddress, l, st, postalCode, facsimileTelephoneNumber, mail, userAccountControl FROM ''LDAP://DC=domain,DC=lan'' WHERE objectClass = ''Person'' AND OBJECTClass <> ''Computer''')
                       AS derivedtbl_1
WHERE     (userAccountControl <> 2) AND (userAccountControl <> 66080) AND (userAccountControl <> 514) AND (telephoneNumber IS NOT NULL) AND (mail IS NOT NULL)
ORDER BY department, displayName

There is a table in SQL as well that I created that has each department name, it's address, telephone, etc... that gets thrown into a dataset which I explain below.

In VS, the dataset used for the report joins the table and view in SQL on the program name of each. This disregards the users who have multiple programs. I would like to find a way to seperate out the folks with multiple programs and instead of splitting the value into separate columns, separate them into separate rows.

So do users have multiple records in the query above? Like

displayname    department
Russell            Tech
Russell            Management

And in your department table there would be separate entries for Tech and Management that you join on the [department] field in your query above?

department      address         telephone
Tech                          1st Street      555-555-5551
Management            19th Street   555-555-5552

And what you want them combined like this (but with more fields, of course):

displayname  department  departmentAddress    departmentTelephone
Russell            Tech                      1st Street                      555-555-5551
Russell            Management        19th Street                   555-555-5552

Can you create a single query like this:

SELECT TOP (100) PERCENT 
	t1.userAccountControl, 
	t1.mail, 
	t1.facsimileTelephoneNumber, 
	t1.postalCode, 
	t1.st, 
	t1.l, 
	t1.streetAddress, 
	t1.mobile, 
	t1.telephoneNumber, 
	t1.title, 
	t1.displayName,
	t2.* -- get all department info, too
FROM OPENQUERY(
	ADSI, 
	'SELECT displayName, department, title, telephoneNumber, mobile, streetAddress, l, st, postalCode, facsimileTelephoneNumber, mail, userAccountControl FROM ''LDAP://DC=domain,DC=lan'' WHERE objectClass = ''Person'' AND OBJECTClass <> ''Computer'''
	) AS t1
	JOIN MyDepartments t2
		ON t1.department = t2.department
WHERE (t1.userAccountControl <> 2) AND (t1.userAccountControl <> 66080) AND (t1.userAccountControl <> 514) AND (t1.telephoneNumber IS NOT NULL) AND (t1.mail IS NOT NULL)
ORDER BY t1.department, t1.displayName

Select allOpen in new window

So do users have multiple records in the query above? Like

displayname    department
Russell            Tech
Russell            Management

No. Each user is listed only once from the query. If the user is in multiple departments, it will just list them once and in the department field both departments would be listed.

And in your department table there would be separate entries for Tech and Management that you join on the [department] field in your query above?

department      address         telephone
Tech                          1st Street      555-555-5551
Management            19th Street   555-555-5552

Correct.

And what you want them combined like this (but with more fields, of course):

displayname  department  departmentAddress    departmentTelephone
Russell            Tech                      1st Street                      555-555-5551
Russell            Management        19th Street                   555-555-5552

Correct.

As for the sql statement you provided, I can't tell what is different from it than the one I provided. I will run it though and tell you what happens.

Thanks for all your help so far Russell. I have been banging my head on this for the better part of the day. It's great to actually work with someone on this!

I just ran it but it didn't show any of the users with multiple departments in the department field.

OK, I definitely got much closer with the above code. Everyone with multiple departments is now showing up multiple times for each iteration of the actual department name. I think at this point I can play with it a bit more and get it to be what I need. I have to say, I never thought to use a LIKE statement on a join. Thanks for your out of the box thinking!

It's probably horribly inefficient, but as long as you aren't hitting massive data sets with it, it should be fine. Let me know if you need any more help!

Thanks Russell for all your help. The code you helped me with is now working to populate the view in SQL which I then bring into a dataset within VS. Today I have been working on the report and everyone is listed on it as they should be. Again, thanks for helping me stop banging my head against the wall!