SQL Server 2008 - 连接字符串

Question

我需要获取每个行项目的查询结果，并构建一个以分号分隔的构成该项目的 Material 列表。

架构化妆:

表格:
LineItems(唯一项目列表)
LineItems_Materials(多对多)
Material (唯一 Material list )

行项目:
ID|订单项
1|'1A.1'

LineItems_Materials:
ID|行项目ID| Material ID
1|1|1
2|1|2
3|1|3

Material :
标识| Material
1|混凝土
2|钢
3|污垢

所以对于 Line Item 1 (1A.1) 我希望它显示 Concrete;Steel;Dirt

我知道我可以编写一个函数来做到这一点。我在函数中使用了 CTE....我也可以使用 while 循环。还有其他更好的方法吗？

这是我所拥有的(脚本将构建对象、加载数据和创建函数):

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'

还有其他想法吗？

始终感谢反馈

--S

Answer 1

如果要跨行连接值，请使用 FOR XML 技巧，例如:

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

这是一个更完整的例子:

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