Similar complex query - 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)

Similar complex query

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-08-30 : 14:39:39

I have query I have read I have to do with cursors.

I have to display multiple rows of one table in a single row of another table, displaying it in a comma delimited list.

Two Tables:
User Fields: Fields (User_ID,Username)
Skill: Fields (Skill_ID,Skillname)
UserSkill: Fields (User_ID,Skill_ID, Experience)

Desired Table Output: Fields (Username, Delimited Skill List) in order of skill experience.

Here is the trick.
For users with more than three skills it must display only the top 3 skills followed by '...' e.g. (1,'Eating, Working, Sleeping ...') - in order of skill experience.

Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-30 : 14:46:55

You can create the comma delimited list using a function.



CREATE FUNCTION udf_Convert_ToCSV
(@UserID int)
RETURNS varchar(4000)
AS

BEGIN

DECLARE @s varchar(4000)

SELECT @s = COALESCE(@s + ',', '') + Experience 
FROM SkillTableName
WHERE User_ID = @UserID

RETURN (@s)

END

Then call the UDF:

SELECT UserName, dbo.udf_Convert_ToCSV(User_ID)
FROM TableName

IF you want help with the rest, you'll need to post the CREATE TABLE statements for both tables and some sample data. Let us know what the result set should look like using that sample data.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources