SQL Server 2008 - 连接字符串

问题描述

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

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.

架构构成:

表格:LineItems(唯一项目列表)LineItems_Materials(多对多)材料(唯一材料清单)

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

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

Line Items:ID|LineItem1|'1A.1'

LineItems_Materials:ID|行项目ID|材料ID1|1|12|1|23|1|3

LineItems_Materials:ID|LineItemID|MaterialID1|1|12|1|23|1|3

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

Materials:ID|Material1|Concrete2|Steel3|Dirt

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

So For Line Item 1 (1A.1) I want it to show Concrete;Steel;Dirt

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

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'

还有其他想法吗?

感谢您的反馈

--S

推荐答案

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

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

这篇关于SQL Server 2008 - 连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！