Single Table multi column - 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

SQL Server Development (2000)

Single Table multi column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-20 : 17:33:56

mayank writes "SQL GURU

i have table called expend with the following data

purch_date    AMOUNT

01/JAN/1995     100
02/JAN/1995     200
03/JAN/1995     300
:
:
15/JUN/1996    350
:
31/DEC/1998     400

what i want a report like this

year     QTR1                    QTR2            qtr3    qtr4
  
1995     Total of               total of amt
         amount for jan-mar    for apr-jun

1996
1997
1998

i have manged to the the result for the qtr1, but i am not able to get teh qtr2 and in the same row but different column. kindly advice a soln.

thanks
Mayank"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-20 : 18:13:15

Mayank

Depending on how desperate you are, I'd suggest you export your data to Excel and do a PIVOT.

You can do this in SQL Server, but if you're a beginner you're going to see a fair bit of code. Advise whether you really want this solution...

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Nazim
A custom title

1408 Posts

Posted - 2002-01-20 : 23:47:42

i would suggest you to follow this article by Robvolk. i think this should do what you are looking for.
http://www.sqlteam.com/item.asp?ItemID=2955

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-21 : 22:00:37

Mayank

I think you'll find the feature in Excel is much quicker to learn and easier to use - especially if you're not a Guru like Nazim obviously is

.

Unfortunately though, I think Excel worksheets are limited to about 50,000 lines....

But if your dataset Mayank, is small, I'd go with the XL.

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-21 : 22:09:14

something like

select year = datepart(yy, purch_date)
q1 = sum(case when datepart(qq, purch_date) = 1 then AMOUNT else 0 end) ,
q2 = sum(case when datepart(qq, purch_date) = 2 then AMOUNT else 0 end) ,
q3 = sum(case when datepart(qq, purch_date) = 3 then AMOUNT else 0 end) ,
q4 = sum(case when datepart(qq, purch_date) = 4 then AMOUNT else 0 end) ,
from expend
group by datepart(yy, purch_date)
order by datepart(yy, purch_date)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources