SQL Server 2008 - Concatenation String

Question

I need to take the query results for each item and create a list of materials with semicolon separators that make up this element.

Makeup Scheme:

Table: LineItems (unique list of elements) LineItems_Materials (many for many) Materials (unique material)

Positions: ID | LineItem 1 | '1a.1'

LineItems_Materials: ID | LineItemId | MaterialID 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3

Materials: ID | Material 1 | Concrete 2 | Steel 3 | Dirt

So, for position 1 (1A.1) I want it to show Concrete; Steel Dirt

I know that I can write a function for this. I used CTE in a function .... I could use a while loop. Is there any other way that would be better?

Here's what I (Script will build objects, load data and create a function):

SCRIPT: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[UFN_LineItem_Materials]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [test].[UFN_LineItem_Materials] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[LineItems]') AND type in (N'U')) DROP TABLE [test].[LineItems] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[Materials]') AND type in (N'U')) DROP TABLE [test].[Materials] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[test].[LineItems_Materials]') AND type in (N'U')) DROP TABLE [test].[LineItems_Materials] GO IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'test') DROP SCHEMA [test] GO CREATE SCHEMA [test] AUTHORIZATION [dbo] GO Create Table test.Materials( MaterialID INT IDENTITY(1,1), Material varchar(100)); Insert Into test.Materials Values('Concrete'); Insert Into test.Materials Values('Steel'); Insert Into test.Materials Values('Dirt'); GO Create Table test.LineItems_Materials( LineItemMaterialID INT IDENTITY(1,1), LineItemID INT, MaterialID INT) GO Insert Into test.LineItems_Materials Select 1,1 UNION Select 1,2 UNION Select 1,3 GO CREATE TABLE [test].[LineItems]( [LineItemID] [int] IDENTITY(1,1) NOT NULL, [ItemNumber] [varchar](25) NULL ) ON [PRIMARY] GO Insert Into [test].[LineItems] Select '1A.1' GO ------------------------------------------------------------- --Build Material Strings (;) example: List of Materials ------------------------------------------------------------ CREATE FUNCTION test.UFN_LineItem_Materials(@LineItemID INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @Materials Varchar(100) = '' ;with CTE AS( Select lm.LineItemID,m.MaterialID,m.Material from test.LineItems_Materials lm inner join test.Materials m on lm.MaterialID = m.MaterialID Where lm.LineItemID = @LineItemID ) Select @Materials += ';' + c.Material from CTE c; SET @Materials = substring(@Materials,2,LEN(@Materials)-1); RETURN @Materials; END GO Select lm.LineItemID,test.UFN_LineItem_Materials(lm.LineItemID) Materials From test.Materials m inner join test.LineItems_Materials lm on m.MaterialID = lm.MaterialID Where m.Material = 'Concrete' 

Any other ideas?

Always appreciate the feedback

- S

Answer 1

If you want to combine values ​​between strings, use the FOR XML trick, for example:

 SELECT Name + ',' FROM Project FOR XML PATH('') 

Here is a more complete example:

 select LineItemID, ( Select m.Material + ',' From test.Materials m inner join test.LineItems_Materials lm1 on m.MaterialID = lm1.MaterialID Where m.MaterialID in (select MaterialID from test.LineItems_Materials where LineItemID = lm2.LineItemID) FOR XML PATH('') ) as Materials from test.LineItems_Materials lm2 group by LineItemID 

Answer 2

try something like this:

 declare @x table (x varchar(5)) insert @x values ('AAAA') insert @x values ('BBBB') insert @x values ('CCCC') SELECT STUFF( ( SELECT ','+x FROM @x FOR XML PATH('') ), 1, 1, '' ) AS ColName 

CONCLUSION:

 ColName ------------------- AAAA,BBBB,CCCC (1 row(s) affected) 

or like this:

 declare @x table (RowID int, x varchar(5)) insert @x values (1,'AAAA') insert @x values (1,'BBBB') insert @x values (1,'CCCC') insert @x values (2,'aa') insert @x values (3,'abc') insert @x values (3,'123') SELECT a.RowID, STUFF( ( SELECT ', '+bx FROM @xb WHERE a.RowID=b.RowID order by bx FOR XML PATH('') ), 1, 2, '' ) AS ColName FROM @xa GROUP BY a.RowID 

CONCLUSION:

 RowID ColName ----------- ----------------- 1 AAAA, BBBB, CCCC 2 aa 3 123, abc (3 row(s) affected) 

Answer 3

The next TSQL will do the trick, you just need to substitute the appropriate column and table names

 DECLARE @vString NVARCHAR(500) SET @vString = '' SELECT @vString = @vString + ColumnNameToConcatenate + ',' FROM TableToPickColumnFrom SELECT SUBSTRING(@vString, 0, LEN(@vString) -1) AS ConcatenatedText 

It also removes the final , from the end of the line so you get:

Value1, Value2, Value3

rathe than:

Value1, Value2, Value3,