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.

Please Login or Register  to view this content.

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