Posted by David Portas on 09/29/05 16:42

Zero.NULL wrote:
> Following is my table and its content:
>
> Year TargetedBudget FirstQuarter SecondQuarter ThirdQuarter
> ------------- ---------------------- ---------------------- ---------------------- ----------------------
> 2000 25000 12000 11000 1000
> 2001 35000 22000 21000 2000
> 2002 45000 32000 31000 3000
> 2003 55000 42000 41000 4000
>

Why? A table is not a spreadsheet.

CREATE TABLE accounts (trans_date DATETIME NOT NULL, trans_amount
NUMERIC(10,2), ....)


> I want a query which returns the result in this format:
>
> Yr_Col1 Yr_Col2 Yr_Col3 Yr_Col4
> ------------- ------------- ------------- -------------
> 2000 2001 2002 2003
> 25000 35000 45000 55000
> 12000 22000 32000 42000
> 11000 21000 31000 41000
> 1000 2000 3000 4000
>

Any reporting tool will generate a cross tab report for you. If you
must do it in a query then it ought be easy to produce this report just
by grouping on the date. Unfortunately your table design is a real
obstruction to that - and to almost any useful reporting for that
matter.

--
David Portas
SQL Server MVP
--

[Back to original message]