Refining MS ACCESS data to csv file using T-SQL

Hello,

I have a table with these sample data pattern:
Col1 Col2
001 2
002 1
002 2
002 1
003 4
003 4
003 3
003 3
003 5

The expected output in the csv file should be exactly like this:
001,2
002,4
002,
002,
003,19
003,
003,
003,
003,

In the csv file, the number next to the topmost 002 is the sum of the Col2 where Col1
is 002 and the remaining field next to other 002 rows will be blank, the same process repeats for 003 and so on.

Would anyone please help me to make a T-SQL statement for the above requirement? I am using VB codes.

Thanks

k

1. Add an AutoNumber field named as ID to your table;

2. Create the query in Access:

SELECT t1.Col1, t2.sumCol2
FROM yourTable AS t1 LEFT JOIN [SELECT Col1, SUM(Col2) as sumCol2, min(ID) as minID
FROM yourTable Group By Col1]. AS t2 ON (t1.ID=t2.minID) AND (t1.Col1=t2.Col1);

3. Export this query to a csv file from Access.

|||

Thank you very much limno. Your T-SQL code perfectly solved my problem.

k
sql