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

Referencing a calculated field in a query

I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005

SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2

This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.

The only way how to avoid the issue in the SQLExpress seems probably to be

- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)

- Utilize computed columns if possible (no idea on performance impact here)

Am I right or is there any other way how to reference a calculated field?

Any suggestion is greatly welcomed! Thanks in advance.The problem is not really with the calculated field. The problem is that column aliases are not assigned until the SELECT statement's results are processed, so you cannot refer to them within the statement.
Two workarounds are to repeat the formula within the result set each time it is needed, or to calculate expression 1 in a SELECT subquery and then refer to it in expression 2 in the outer query.
Of the two, I prefer the first option most of the time.