SQL 服务器 - 完整的发票编号 + 逗号分隔的 SQL 列表 (TSQL)

Question

有人可能知道如何从SQL中获取一个不重复的逗号分隔列表 - 这有点难以解释。我举个例子。

我有一个发票列表 + 它所属的货物，表格如下：

InvoiceNumber ShipmentNumber
0180376000    1stShipment
0180376005    1stShipment
0180376003    1stShipment
0180375997    1stShipment
0180375993    1stShipment

此列表需要分为主发票编号，后跟剩余发票编号的右 2 位数字。结果应类似于以下内容。

01803760, 00, 05, 03, 01803759, 97, 93

在这一点上，我可以很容易地获得逗号分隔的列表，但无法弄清楚如何在它所属的每张发票之后放置 2 位数字。

任何关于如何做到这一点的建议都会很棒！！

Answer 1

像这样试试

DECLARE @tbl TABLE(InvoiceNumber VARCHAR(100));
INSERT INTO @tbl VALUES
 ('0180376000')
,('0180376005')
,('0180376003')
,('0180375997')
,('0180375993');
WITH CutInTwo AS
(
    SELECT LEFT(InvoiceNumber,8) AS Number
          ,RIGHT(InvoiceNumber,2) AS SubNumber
    FROM @tbl
)
,OnlyMainNumbers AS
(
    SELECT DISTINCT Number
    FROM CutInTwo
)
SELECT y.Number + ' ' + (STUFF((SELECT ', ' + x.SubNumber FROM CutInTwo AS x WHERE x.Number=y.Number FOR XML PATH('')),1,2,''))
FROM OnlyMainNumbers AS y

如果你真的需要它在一个字符串中，你可以像这样包装最终的选择：

SELECT STUFF(
(
    SELECT ', ' + y.Number + ', ' + (STUFF((SELECT ', ' + x.SubNumber FROM CutInTwo AS x WHERE x.Number=y.Number FOR XML PATH('')),1,2,''))
    FROM OnlyMainNumbers AS y
    FOR XML PATH('')
),1,2,'')

Answer 2

试试这个

DECLARE @tbl TABLE (InvoceNumber NVARCHAR(50))
INSERT INTO @tbl VALUES  ('0180376000')
INSERT INTO @tbl VALUES  ('0180376005')
INSERT INTO @tbl VALUES  ('0180376003')
INSERT INTO @tbl VALUES  ('0180375997')
INSERT INTO @tbl VALUES  ('0180375993')
SELECT  
    (
        SELECT
             A.InvoceNumber + ', ' 
        FROM
        (
            SELECT DISTINCT LEFT(InvoceNumber, LEN(InvoceNumber) - 2) InvoceNumber  FROM @tbl
            UNION ALL
            SELECT RIGHT(InvoceNumber, 2) InvoceNumber FROM @tbl
        ) A
        FOR XML PATH ('')
    ) Invoce

输出：

01803759, 01803760, 00, 05, 03, 97, 93, 

如果顺序很重要。

SELECT
(
    SELECT
        Result.InvoceNumber + ', ' + Result.Invo
    FROM
    (
        SELECT
            A.InvoceNumber,
            (
                SELECT
                     IA.Invo   + ', ' AS [text()]
                FROM
                (
                    SELECT DISTINCT LEFT(InvoceNumber, LEN(InvoceNumber) - 2) InvoceNumber, RIGHT(InvoceNumber, 2) AS Invo  FROM @tbl
                ) IA
                WHERE
                    IA.InvoceNumber = A.InvoceNumber
                FOR XML PATH ('')
            ) Invo
        FROM
        (
            SELECT DISTINCT LEFT(InvoceNumber, LEN(InvoceNumber) - 2) AS InvoceNumber FROM @tbl
        ) A
        GROUP BY 
            A.InvoceNumber
    ) Result
    FOR XML PATH ('')
) S

输出：

01803759, 93, 97, 01803760, 00, 03, 05,