| Author |
Topic |
|
patshaw
Posting Yak Master
177 Posts |
 Posted - 2006-10-19 : 12:53:29
|
| I am working with a SQL Server 2000 table that looks like this:AGENT CID1 CID2 CID3 CID412435 3465 4575 46534 6789 73867 5642 25346 6785This is difficult to show on the forum so I will explain.Each Agent can have one or multiple CID's, so Agent 55434 could have a CID1 of 7638 (for example) and a CID3 of 8364 with CID3 and CID4 being NULL but Agent 66244 could have only a CID3 of 4722 with the other CID's being NULL. A third Agent 68221 could then have all 4 CID's populated with a number.I need to take all these values and put them into a new table that lists each CCID and the agent it belongs to on a row by row basis. The table, from the above example would look something like this:AGENT CID55434 763855434 836466244 472268221 634568221 907568221 436268221 8743I cannot figure out how I can do this. If someone could show me I would be eternally grateful. This process will run overnight so processing isn't a problem if the solution is costly.Thanks. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-19 : 12:56:13
|
| insert tblnewselect AGENT, CID1 from tblunion allselect AGENT, CID2 from tbl where CID2 is not nullunion allselect AGENT, CID3 from tbl where CID3 is not nullunion allselect AGENT, CID3 from tbl where CID4 is not nullThey can be seperate insert statements rather than a union if you wish.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-19 : 15:01:29
|
| Thank you very much, I will try this first thing in the morning when I get to work. I presume I can also do a join to another table if required - as far as I know there is no restriction on Unions where joins are concerned?Just out of interest, (although your footnote states the case) could this be done with a cursor? Would you be kind enough to show me the syntax to accomplish this with a cusror. I have never really used cursors and I am just interested to see how you would perform something like this by using cursors. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2006-10-19 : 15:04:45
|
| You don't want to use a cursor for this. Try to avoid them whenever you can. |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-19 : 15:59:09
|
| I was just asking out of interest really, with my lack of knowledge where cursors are concerned I just wondered if and how this could be done with a cursor. I understand they are costly and have no intention to use one in this case but i was just interested. |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-19 : 16:00:47
|
| Also, am I correct in thinking that I can use a join in the Union? Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-19 : 16:18:13
|
quote:
Originally posted by patshaw
Also, am I correct in thinking that I can use a join in the Union? Thanks.
Yes you can.Tara Kizer |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-19 : 18:13:43
|
| Thanks Tara,I can see that you guys are very anti-cursor in this scenario but if someone could spare me a little more of their time to show me how it can be done (for the sake of my SQL education) I would be eternaly grateful. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-19 : 18:22:07
|
| Your SQL education should not include cursors. If you need to see examples of them, check out DECLARE CURSOR in SQL Server Books Online. No one in their right mind will show you how to do it the wrong way.Tara Kizer |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-20 : 05:48:03
|
| Don't really see how a cursor would be applicable to this. It would have to create the resultset to loop through the rows so the cursor would just be the same query.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
patshaw
Posting Yak Master
177 Posts |
Posted - 2006-10-20 : 06:14:12
|
| Yeah, I can see what you mean. Thanks guys for all your help. The union works perfectly.Pat. |
|
|
|
|
How can I do this?