question - SQL Server Forums

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)

question

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-25 : 07:38:35

writes "Hi,

I've a query that retrieves the data like...

I'm retrieving the details of all the users who belong to more than one detartment.

UserId DEPT

1 20
1 23
2 20
2 21
5 21
5 23

Now what I need is to display the same data like,

UserID DEPT1 DEPT2

1 20 23
2 20 21
5 21 23

without using any temp tables / cursors.

And at the moment my query is,

SELECT DISTINCT a.userid, a.deptcode
FROM users a
JOIN users b
ON a.userid = b.userid
AND a.deptcode <> b.deptcode
GROUP BY a.userid , a.deptcode
ORDER BY a.userid

SQL server 2000 version 8.0 on Windows 2K pro"

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-25 : 09:42:30

Let's see I want recursion, but I can't use any features in SQL to accomplish it. OK. The only thing I can think of here is using some sort of delimited list creator. You can do a search here for "Converting Multiple Rows into a CSV String (Set Based Method)" but it will give you rows with a comma-delimited list, not separate fields.

UserID	Departments
------	-----------
1	20,23
2	20,21
5	21,23

You can then pull that out into your report and separate them in the presentation layer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources