如何从一列插入多行 [英] How to Insert Multiple Row from one Column

问题描述

我想在一列中插入多行。

I want to insert multiple rows into one column.

对于这样的POS系统。

For my POS system like this.

表 A ：

Transaction ID| Item Code|Qty|Total|Transaction Date|
-----------------------------------------------------
00001         |  Item 1  |3  |100  |12/07/2014      |
00001         |  Item 2  |2  |50   |12/07/2014      | 
00001         |  Item 3  |1  |150  |12/07/2014      |

此后，我想在表格中看到它

After that I want to see this in my table

Transaction ID|Item Code             |Total of Qty|Total of Price|Transaction Date|
-----------------------------------------------------------------------------------
00001         |Item 1, Item 2, Item 3|      6     |      150     | 12/07/2014     |

推荐答案

确实没有内置的concat函数在SQL Server中，我怀疑会有这样的情况。原因是创建 CLR用户定义的聚合非常容易。实际上，MSDN上已经有这样的例子。您可以在这里找到创建 GROUP_CONCAT 函数所需的所有内容-字符串实用程序功能。

It is true there is no build-in concat function in SQL Server and I doubt there will be such. The reason is it is very easy to create a CLR User-Defined Aggregates. Actually, there is already such example on MSDN. Everything you need to create a GROUP_CONCAT function can be found here - String Utility Functions.

基本上，您需要执行以下步骤：

Basically, you need to follow the steps below:

1. 启用 CLR 集成：

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

创建以下C＃类并构建 .dll ：

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Microsoft.SqlServer.Server.Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true,//optimizer property
    IsInvariantToDuplicates = false,//optimizer property
    IsInvariantToOrder = false,//optimizer property
    MaxByteSize = 8000)]    //maximum size in bytes of persisted value
public class Concatenate : Microsoft.SqlServer.Server.IBinarySerialize
{
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    private StringBuilder intermediateResult;

    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
    {
        intermediateResult = new StringBuilder();
    }

    /// <summary>
    /// Accumulate the next value, nop if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(SqlString value)
    {
        if (value.IsNull)
        {
            return;
        }

        intermediateResult.Append(value.Value).Append(',');
    }

    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(Concatenate other)
    {
        intermediateResult.Append(other.intermediateResult);
    }

    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        string output = string.Empty;
        //delete the trailing comma, if any
        if (intermediateResult != null && intermediateResult.Length > 0)
            output = intermediateResult.ToString(0, intermediateResult.Length - 1);
        return new SqlString(output);
    }

    public void Read(BinaryReader r)
    {
        if (r == null) throw new ArgumentNullException("r");
        intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        if (w == null) throw new ArgumentNullException("w");
        w.Write(intermediateResult.ToString());
    }
}

部署程序集并创建函数：

Deploy the assembly and create your function:

DECLARE @SamplePath nvarchar(1024)
SET @SamplePath = 'C:\MySample\'

CREATE ASSEMBLY [StringUtils] 
FROM @SamplePath + 'StringUtils.dll'
WITH permission_set = Safe;
GO

CREATE AGGREGATE [dbo].[Concatenate](@input nvarchar(4000))
RETURNS nvarchar(4000)
EXTERNAL NAME [StringUtils].[Concatenate];
GO

然后您可以使用此函数可以作为任何标准聚合函数使用：

Then you can use this function as any standard aggregate function:

SELECT TransactionID, [dbo].Concatenate(ItemCode) AS ItemCodes,  
       SUM(Qty) AS TotalofQty, SUM(Total) AS TotalPrice, TransactionDate
FROM TableA
GROUP BY TransactionID, TransactionDate;

注意，我已经知道 CLR 集成多年，但几个月前才开始使用它。当处理大量数据时，性能差异很大。

Note, I have known about CLR integration for years, but started to use it few months ago. The performance difference is huge when you are working with large collections of data.

这篇关于如何从一列插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！