Help with query gets concatenate data from a secon

I have table users

IDuser Name
1 John
2 Peter
3 JOsh

IDuser Skills
1 -- typist
1 -- acct
3 -- driver
3 -- Guard

I need to write a query that will show as follows

ID NAME -- SKILLS
1 -- John -- typist,acct
3 -- Josh -- Driver,Guard

with a skills column that has combine the values of the second table

Create a function that takes in an ID and gives you a concatenated list of Skills. Call the function in a SQL Query that joins the tables.

Start with the function and post back what you have and someone can help you out.

|||

I was using this type of function to do that

 
CREATEFunction [dbo].[udf_ConcatenateFields] (@.CustomerIDint)RETURNSvarchar(200)ASBEGINDECLARE @.FreightListvarchar(100)SELECT @.FreightList =COALESCE(@.FreightList +', ','') +CAST(FreightDescriptionAS varchar(30))from CustomerMaster_FreightTypes fjoin CustomerMaster_CustomerFreightTypes con f.FreightTypeID = c.FreightTypeIDWHERE CustomerID = @.CustomerIDreturn @.FreightListEND
 

however since in this sample the table is hardcoded it means I will have to make a function for every table that this is need it, I was hoping to find a routine/code that can be reusable

|||

I would have to say... Go to google.com. Search for "T-SQL Concatenate aggregation function". Read each of the top 10 results.

|||

Follow the description Motley gave us and this is what i've got:http://www.projectdmx.com/tsql/rowconcatenate.aspx

The author of this article has listed more than 5 methods which you can use to concatenate rows. I think this must be helpful for OP to solve his issue. I will cite one solution here:

Concatenating values when the number of items is small and known upfront

When the number of rows are small and almost known upfront, it is easier to generate the code. One common approach with a small set of finite rows it the pivoting method. Here is an example where only first four alphabetically sorted product names per categoryid is retrieved:

 SELECT CategoryId, MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' + MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' + MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' + MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END ) FROM ( SELECT p1.CategoryId, p1.ProductName, ( SELECT COUNT(*) FROM Northwind.dbo.Products p2 WHERE p2.CategoryId = p1.CategoryId AND p2.ProductName <= p1.ProductName ) FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq ) GROUP BY CategoryId ;

Hope my suggestion helps

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

|||

thank you very much for the assistance

from the link in the above post, I was able to find this sample that worked for me perfecly. I been searching for 3 days and thanks to you I found it.

this solution easily integrates in an already current query

SELECT p1.CategoryId,
( SELECT ProductName + ','
FROM Northwind.dbo.Products p2
WHERE p2.CategoryId = p1.CategoryId
ORDER BY ProductName
FOR XML PATH('') ) AS Products
FROM Northwind.dbo.Products p1
GROUP BY CategoryId ;

a comma ends up at the end of the field but that will be simpler to handle it.

thank you once again

|||

Glad I could point you in the right direction. Often there are answers on the net, if you only knew the secret magic words they were found under.