#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] SQL Sum and Concatenate two Columns

## dirock

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

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

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:



```

   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

----------


## dirock

Thank you so much! Works perfectly!!

----------

