How to Insert Multiple Row from one Column

Question

I want to insert multiple rows into one column.

For my POS system like this.

Table 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     |

Answer 1

Use GROUP_CONCAT to concatenate strings together, in a GROUP BY:

SELECT TransactionID, GROUP_CONCAT(ItemCode) AS ItemCodes,  
       SUM(Qty) AS TotalofQty, SUM(Total) AS TotalPrice, TransactionDate
FROM TableA
GROUP BY TransactionID, TransactionDate;

SqlFiddle here

Edit After changing the RDBMS to SqlServer, a hack is required to compensate for SqlServer's lack of string folding functions like GROUP_CONCAT. Here's the STUFF / FOR XML PATH one:

SELECT a.[Transaction ID], 

        STUFF((
          SELECT ',' + [Item Code]
          FROM TableA
          WHERE [Transaction ID] = a.[Transaction ID]
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
     AS ItemCodes,  
       SUM(Qty) AS TotalofQty, SUM(Total) AS TotalPrice, [Transaction Date]
FROM TableA a
GROUP BY a.[Transaction ID], a.[Transaction Date];

Note that you'll need to manually correlate the STUFF subquery with the respective outer query.

SqlServer Fiddle

Answer 2

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. Enable CLR integration:

   sp_configure 'clr enabled', 1
   GO
   RECONFIGURE
   GO
   

2. Create the following C# class and build the .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());
       }
   }
   

3. 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;

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.