SQL Server 2008 - Concatenate String

Question

Hi All,

I have the need to take the results of a query per line item and build a semi-colon delimited list of materials that make up that item.

Schema Makeup:

Tables: LineItems (Unique Item Listing) LineItems_Materials (Many to Many) Materials (Unique Material Listing)

Line Items: 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 Line Item 1 (1A.1) I want it to show Concrete;Steel;Dirt

I know I can write a Function to do this. I used CTE in the function....I could use a while loop as well. Is there another method that would be better?

Here is what I have (Script will build objects, Load data, and Create 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 concatenate values across rows, use the FOR XML trick, e.g.:

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

The following 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 remove the trailing , from the end of the string so you get:

Value1,Value2,Value3

rathe than:

Value1,Value2,Value3,

Answer 3

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

OUTPUT:

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 ', '+b.x
                   FROM @x b
                   WHERE a.RowID=b.RowID
                   order by b.x
                   FOR XML PATH('') 
             ), 1, 2, ''
         ) AS ColName
    FROM @x a
    GROUP BY a.RowID

OUTPUT:

RowID       ColName
----------- -----------------
1           AAAA, BBBB, CCCC
2           aa
3           123, abc

(3 row(s) affected)