By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,036 Members | 960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,036 IT Pros & Developers. It's quick & easy.

Horizotal to Vertical Display

P: n/a
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

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

There could be many work around, but what would be fasted single query
for this?

Sep 29 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
A crosstab-style query would be the usual SQL solution, dynamically
generated if the number of years is not known in advance - Google for
"dynamic crosstab query", "pivot query" and "reverse pivot query" for
examples. How well that performs would depend on your data volume,
indexes etc., so it's difficult to give a specific answer.

You should also consider doing the crosstab on the client side, where
it may be easier to write and maintain than using dynamic SQL (which
can also be a security issue). Or use a third-party reporting tool to
do this for you, rather than write your own code - MSSQL Reporting
Services is free, for example.

Simon

Sep 29 '05 #2

P: n/a
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
--

Sep 29 '05 #3

P: n/a
Hello David,

Yes I can use Reporting Tools, I can use third party tools as well but
I was more interested in getting some crosstab-style query and its pros
and cons, as Simon suggested some.

Well if the design I specified here is not appropriate, Let's have a
look on design you mentioned here:

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

It will have data something like this:

2004-03-01 100
2004-04-01 500
2004-07-01 400
2004-08-01 600
2005-03-01 200
2005-04-01 500
2005-07-01 300
2005-08-01 400
2005-11-01 700

It will eazily serve me my output but what if I will have to show it in
my initial display (again crosstab-style query)?

Here Initial display means:

Year FirstQuarter SecondQuarter ThirdQuarter ForthQuarter

2004 - 100 500 1000 0
2005 - 200 500 700 700

Sep 29 '05 #4

P: n/a
I am also aware that there are some drawback in using Dynamic SQL

Sep 29 '05 #5

P: n/a
> Well if the design I specified here is not appropriate

If you don't agree then I recommend you read up on database design
practice.
what if I will have to show it in
my initial display (again crosstab-style query)?


Like this (I'm assuming calendar quarters. If not, then use a different
calculation or us a Calendar table for the quarters).

SELECT
YEAR(trans_date),
SUM(CASE WHEN MONTH(trans_date) BETWEEN 1 AND 3
THEN trans_amount END),
SUM(CASE WHEN MONTH(trans_date) BETWEEN 4 AND 6
THEN trans_amount END),
SUM(CASE WHEN MONTH(trans_date) BETWEEN 7 AND 9
THEN trans_amount END),
SUM(CASE WHEN MONTH(trans_date) BETWEEN 10 AND 12
THEN trans_amount END)
FROM accounts
GROUP BY YEAR(trans_date) ;

(untested)

--
David Portas
SQL Server MVP
--

Sep 29 '05 #6

P: n/a
I don't find myself a very good on database design and its not like
that I do not agree on the above. I would really like to grow my vision
in this direction.

Could you please suggest me some of the best practice ways?
Thanks in advance.

Sep 30 '05 #7

P: n/a
One of the basic ideas of a tiered architecture is that reporting in
done in the application layer, NOT in the database.

You have been posting questions that tell us "Golden Oldies" that you ,
"Newbie", have not done your homework. A newsgroup is a baaaad place
to learn the foundations. A lot of people will give you a kludge to
make you shut up. A few of us more pendantic ones will try to lecture
you; but it is not possible to post an entire one-quarter college on
RDBMS.

Oct 2 '05 #8

P: n/a
Its not always possible that the databases, you work with, are designed
by you only, And once you will have to work with such a database which
contains table structures specified here, you can't change it and its
implementation also you can't refuse to work, no matter you like the
design or not.

The question is lost in the discussion with "Golden Oldies", I hope
they will answer - "ONLY IF THEY KNOW IT". And I think no place is bad
to learn anything and individual can't stop me doing so. They have
rights to avoid answering. As there are different level of brains
available on this group, I will get my answers.

Let me be specificabout the topic of discussion:
I wanted to know the best way to acheive Cross-tab query. Its pros and
Cons.
Simon and David hapled me with their experience, thanks to both of
them, still lot to know.

Thanks everybody.

Oct 3 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.