SQL Sum and Concatenate two Columns

**dirock** · 08-20-2012, 09:18 AM

Using and SQL query, I need to consoladate two records into one by suming the qty (Column G) and concatanating the person (column I). I am not sure how to code this in SQL. I attached a spreadsheet as a before and after reference to further explain. Thanks for your help!

**stanleydgromjr** · 08-21-2012, 06:56 AM

dirock,

Detach/open workbook Consolidate_acct_no - Sum Join - dirock - EF854189 - SDG15.xlsm and run the Consolidate_acct_no macro.

If you want to use the macro on another workbook:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Option Explicit
Sub Consolidate_acct_no()
' stanleydgromjr, 08/21/2012
' http://www.excelforum.com/excel-programming-vba-macros/854189-vba-loop-through-table-combine-like-columns-and-add-and-concatenate-respective-cell.html
Dim r As Long, lr As Long, n As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A3:I" & lr).Sort key1:=Range("A3"), order1:=1
For r = 3 To lr
  n = Application.CountIf(Columns(1), Cells(r, 1).Value)
  If n > 1 Then
    Cells(r, 7).Value = Application.Sum(Range(Cells(r, 7), Cells(r + n - 1, 7)))
    Cells(r, 9).Value = Join(Application.Transpose(Range(Cells(r, 9), Cells(r + n - 1, 9))), ", ")
    Rows(r + 1).Resize(n - 1).Delete
    lr = Cells(Rows.Count, 1).End(xlUp).Row
  End If
Next r
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the Consolidate_acct_no macro.

**Kyle123** · 08-21-2012, 07:15 AM

This really isn't easy to do in SQL, especially not access which is quite limited unless you have a very limited number of rows that you know in advance; in which case you could use something like:

PHP Code:


   SELECT CategoryId,
           MAX( CASE seq WHEN 1 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 2 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 3 THEN ProductName ELSE '' END ) + ', ' +
           MAX( CASE seq WHEN 4 THEN ProductName ELSE '' END )
      FROM ( SELECT p1.CategoryId, p1.ProductName,
                    ( SELECT COUNT(*)
                        FROM Northwind.dbo.Products p2
                        WHERE p2.CategoryId = p1.CategoryId
                        AND p2.ProductName <= p1.ProductName )
             FROM Northwind.dbo.Products p1 ) D ( CategoryId, ProductName, seq )
     GROUP BY CategoryId ;

Taken from here: http://www.simple-talk.com/sql/t-sql...-transact-sql/

The other methods mentioned won't work as I don't believe Access supports T-SQL

I'd be tempted to do it after you have pulled the raw data