Complex query 4 me :D - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex query 4 me :D

Author

Topic

bubu80
Starting Member

4 Posts

Posted - 2005-05-13 : 08:21:28

hi all,

code:

doc_id | article | price | quantity | year_doc|
-------------------------------------------------
1 | 01 | 2 | 10 | 2000 |
2 | 02 | 1 | 5 | 2000 |
3 | 03 | 3 | 10 | 2000 |
16 | 01 | 4 | 10 | 2001 |
6 | 02 | 2 | 10 | 2001 |
7 | 03 | 1 | 10 | 2001 |
8 | 04 | -- | -- | 2001 |
22 | 01 | -- | -- | 2002 |
13 | 02 | 3 | 10 | 2002 |
14 | 03 | 3 | 10 | 2002 |
15 | 04 | 3 | 10 | 2002 |
-------------------------------------------------

with this table .. I would like create a query to generaty this
table Report... how can i do that??

The value that are present in a column years... is calculated with
Price* quantity...

code:
article | Year 2000 | Year 2001 | year 2002 |
----------------------------------------------
01 | 20 | 40 | -- |
02 | 5 | 20 | 30 |
03 | 30 | 10 | 30 |
04 | -- | -- | 30 |
----------------------------------------------

How can i create that???
It's possible??

sorry for my bad english...

thanks

Bye

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-13 : 08:56:51

Yeah you'd need something like

SELECT article,
SUM (CASE WHEN year_doc = 2000 then price else 0 end) AS [Year 2000],
SUM (CASE WHEN year_doc = 2001 then price else 0 end) AS [Year 2001],
SUM (CASE WHEN year_doc = 2002 then price else 0 end) AS [Year 2002]
FROM yourtable
GROUP BY article

-------
Moo. :)

bubu80
Starting Member

4 Posts

Posted - 2005-05-13 : 09:19:17

thanks thousands... you saved me!!!

sridotnet
Starting Member

1 Post

Posted - 2005-05-26 : 21:16:01

My question is exactly the same
but the Year2000, Year2001
are unknow. That is
The user will select a range from 1980 to 2005

How do you do that
Hope I am clear

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-27 : 02:06:00

quote:
Originally posted by sridotnet

My question is exactly the same
but the Year2000, Year2001
are unknow. That is
The user will select a range from 1980 to 2005

How do you do that
Hope I am clear

Use a crosstab (pivot) query.
See Jeff's (Dr.Cross Join) blog for excellent code that does this.

rockmoose

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-27 : 03:31:24

Refer this
http://weblogs.sqlteam.com/jeffs/category/156.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources