逗号分隔结果集+ SQL查询 [英] Comma Delimited Result set + SQL Query

问题描述

我有两个表，数据如下所示:

I got two tables with data as listed below:

表1:学生

表2:主题

我需要输出为:

我通过使用用于XML PATH的以下查询实现了这一点

I got this acheived with below query using for XML PATH

代码:

WITH    cte
      AS ( SELECT   Stu.Student_Id ,
                    Stu.Student_Name ,
                    ( SELECT    Sub.[Subject] + ','
                      FROM      [Subject] AS Sub
                      WHERE     Sub.Student_Id = Stu.Student_Id
                      ORDER BY  Sub.[Subject]
                    FOR
                      XML PATH('')
                    ) AS [Subjects]
           FROM     dbo.Student AS Stu
         )
SELECT  Student_id [Student Id] ,
        student_name [Student Name] ,
        SUBSTRING(Subjects, 1, ( LEN(Subjects) - 1 )) AS [Student Subjects]
FROM    cte

我的问题是没有使用XML路径的更好方法吗?

My question is there a better way to do this without using XML Path?

推荐答案

这是一种非常好的方法，并且已经被广泛接受.有几种方法，并且

This is a very good approach and has become pretty well accepted. There are several approaches and this blog post describes a lot of them.

存在的一种有趣的方法是使用CLR为您完成工作，这将通过运行外部代码的折衷来显着降低查询的复杂性.这是该类在程序集中的外观示例.

One interesting approach that exists is using the CLR to do the work for you which will significantly reduce the complexity of the query with the trade-off of running external code. Here is a sample of what the class might look like in the assembly.

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,  MaxByteSize=8000)]
public struct strconcat : IBinarySerialize{

    private List values;

    public void Init()    {
        this.values = new List();
    }

    public void Accumulate(SqlString value)    {
        this.values.Add(value.Value);
    }

    public void Merge(strconcat value)    {
        this.values.AddRange(value.values.ToArray());
    }

    public SqlString Terminate()    {
        return new SqlString(string.Join(", ", this.values.ToArray()));
    }

    public void Read(BinaryReader r)    {
        int itemCount = r.ReadInt32();
        this.values = new List(itemCount);
        for (int i = 0; i <= itemCount - 1; i++)    {
            this.values.Add(r.ReadString());
        }
    }

    public void Write(BinaryWriter w)    {
        w.Write(this.values.Count);
        foreach (string s in this.values)      {
            w.Write(s);
        }
    }
}

这将使查询更像这样.

SELECT CategoryId,
           dbo.strconcat(ProductName)
      FROM Products
     GROUP BY CategoryId ;

显然，这要简单得多.拿它值钱:)

Which is quite a bit simpler obviously. Take it for what it's worth :)

美好的一天！

这篇关于逗号分隔结果集+ SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！