Help with Cursor please
-
Hello,
I have a table in which profile information is stored. I need to display the data on a question to answer basis, even if there is more than one answer I need to show it as one, for instance if a person was asked what price range they are interested in they can chose more than one.
Currently in the table it will look like this
UserID Question Answer
Y1TH2 4 Under $200,000
Y1TH2 4 $200-250,000
Y1TH2 4 $250-300,000
XZ23K 4 Under $200,000
XZ23K 4 $200-250,000
I need to display it as
UserID Question Answer
Y1TH2 4 Under $200,000, $200-250,000, $250-300,000
XZ23K 4 Under $200,000, $200-250,000
I have been told this can be done in a cursor but I'm pretty new to cursors. Any help would be greatly appreciated!
-
Sure, this can be done via a cursor, but why?
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Is there a better way? I would rather not do it in a cursor.
-
Have you read the link I've posted? It doesnt mention a cursor, unless I am mistaken.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Just tried it and I'm using the function and it seems to work...although it takes a little time to run. Thanks so much for your help.
-
Check this code... it would fit...
create table t1(i int, v varchar(10))
insert into t1 values (1,'H')
insert into t1 values (1,'e')
insert into t1 values (1,'l')
insert into t1 values (1,'l')
insert into t1 values (1,'o')
insert into t1 values (2,'B')
insert into t1 values (2,'y')
insert into t1 values (2,'e')
go
create function p(@i int)
returns varchar(30)
as begin
declare @pe varchar(30)
set @pe = ''
select @pe = @pe + v
from t1
where i = @i
return @pe
end
go
select distinct i into #t2
from t1
select i, dbo.p(i)
from #t2
drop table t1
drop table #t2
drop function p
-
Interesting, in the code that was linked to on SqlJunkies.com, there is actually a bug in the temp table method:
SELECT State, au_lname, IDENTITY(INT, 1, 1) AS Ident
INTO #AuthorConcat
FROM Authors
ORDER BY State, au_lname
This doesn't work and has bitten me several times in the past. Even though there is an ORDER BY and it appears as if it should generate ascending IDENT values based on the natural key ... it doesn't, or at least it might on small datasets but it is not guaranteed to.
The only way to acheive this is to create the table first (either with a CREATE TABLE or SELECT INTO ... WHERE 'apples' = 'oranges') and then insert into it with an ORDER BY.
-
Another alternative post
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=160502#bm160753
Very similar to Franks link but variety is the spice of life
. -
Yes, there are definitely more than one way to skin that cat.
Good catch on the temp table method, PW. Have you considered making SQL Team's MVP Rob Volk aware of it?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
>>Have you considered making SQL Team's MVP Rob Volk aware of it?
Actually, the way I read it, it looks like it was Adam Machanic's error - he attempted to improve on Rob's method by adding an Identity to the temp table. Don't want to bump up a 3 month old thread at another forum just to say "your're wrong"
-
PW, you're correct -- the IDENTITY is not guaranteed to come out in any specific order. But this isn't true only on SELECT INTO -- it's also true if you create the table first and insert into it with an ORDER BY on the insert. Further reason not to use the temp table method! Note that without the IDENTITY, the temp table method would have no chance of working in the scenario I outline in my blog post -- which is why it was added... Actually, it could have been done using a subquery to calculate the row number, but that would have added an additional inefficiency that would have thrown the performance numbers way off.
Finally, note that at least in the case of creating the table first and inserting, the behavior of insertion order based on the ORDER BY clause of a SELECT will be guaranteed in SQL Server 2005, according to a conversation I had on the SQL Server 2005 newsgroups. I don't know about SELECT INTO, however.
--
Adam Machanic
whoisactive -
Thanks for the reply! Regarding this:
>>But this isn't true only on SELECT INTO -- it's also true if you create the table first and insert into it with an ORDER BY on the insert.
Do you have URL(s) for references to this ? I was able to find the KB article regarding 'SELECT INTO ...'
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273586
However, I haven't found anything (yet) describing the same behaviour when using INSERT INTO ... ORDER BY. Which is a concern because I'm currently using the technique in a couple of production systems - it was obscure bugs found in these systems which led me to the above KB article and to test the 2 techniques. I was able to replicate Identities coming out contrary to the ORDER BY in SELECT INTO's, but never saw the problem with INSERT INTO, even on large result sets.
-
Since my production Sql Server code (and the probability of my posterior unit taking a serious thrashing from my boss) depend on this, I investigated more.
See this microsoft newsgroup thread (apologies for long Google URL):
Please see replies #21 thru #28 on that thread, and the confirmation from Microsoft of use of the INSERT INTO ... ORDER BY method. Also note the date, this was less than a month ago.
-
I just recently had that conversation about it being fixed in SQL Server 2005, so I'm a bit confused by Ben-Gan's response there. I've posted a request to the private MVP newsgroup for some clarification on this issue; I'll let you know as soon as I hear anything.
--
Adam Machanic
whoisactive -
PW,
Thanks for the Link, I was starting to get a little worried too because I have used that A LOT
* Noel
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply