how to perform query by cursor

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

General SQL Server Forums

New to SQL Server Programming

how to perform query by cursor

Author

Topic

raihan
Starting Member

3 Posts

Posted - 2009-04-06 : 03:14:38

Hi,

I am a newbie in SQL. Please ignore my incompetence and help me out as I'm struggling with a simple query (i guess)...

I've a SQL table that looks similar to this:

Queue Date Location Calls LoginTime (in hrs)
----- ---- -------- ----- ------------------
a x Syd 45 21.8
a y Syd 12 0.9
b x Mel 3 11.5

I need to read the entire table and write down a query so that it outputs in the following format:

Date a_Syd_Calls a_Syd_LoginTime b_Syd_Calls b_Syd_LoginTime a_Mel_Calls a_Mel_LoginTime b_Mel_Calls b_Mel_LoginTime
---- ----------- --------------- ----------- --------------- ----------- --------------- ----------- ---------------
x 45 21.8 0 0 0 0 3 11.5
y 12 0.9 0 0 0 0 0 0

is there any way I can do this? Can anyone shed some light please?

Raihan.

raihan
Starting Member

3 Posts

Posted - 2009-04-06 : 03:18:18

sorry... the output didn't come out in the right formatting... the values beneath each heading corresponds to each one of them. For e.g. for the above table Queue values are a,a,b >> then Date values are x,y,x >> then Location values are Syd,Syd,Mel >> Calls values are 45,12,3 >> thats how it is...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-04-06 : 04:39:08

Hi Raihan,

The technique is called pivot or cross tab. Generally its better to do this in whatever front end application you are using.

If you really need to do this in SQL do a search for

1) CROSS TAB
2) PIVOT

on this site or on books online (if you have sqlserver2005 there is a pivot operator you can use)

Take a stab at it first and post how far you have got -- it's not hard but it does take a bit of getting your head around sometimes.

All the best,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-06 : 04:40:45

I think you are asking: how to pivot a table.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120087

The title of this post mentions cursors. There is no need for a cursor, which you should avoid anyway as they slow performance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 05:12:12

Also refer

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.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