Hi

I want to achieve the following transformation of data using a stored
procedure.

Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================

Thanks in advance. :)

I suspect the reason (...AND ... IS NOT NULL) is helping you out is
that it's forcing the optimizer to choose a table scan instead if
hitting up that index you made on email.

Before you go changing minutiae like ANSI_NULLS, do realize that your
tables are, (relationally speaking), nonsense. Any table without a
PRIMARY KEY or otherwise unique key is likely going to cause problems
with the optimizer, and it often happens in bizarre ways like this.

You should do the following:
1) Make the clustered index on "bk" UNIQUE. If you can't do that...

2) Add email to your clustered index as a secondary key and recreate
the clustered index as UNIQUE, or make the combination of the two
columns a PRIMARY KEY. If you can't do that...

3) Analyze your data and fix your process to not allow wholly duplicate
rows, which are nonsensical. If you can't do THAT (ugh):

4) Add an IDENTITY column to your table. So as not to drastically
increase your index sizes, you could either add this column as a
secondary key after bk and make the clustered index UNIQUE, or you
could create a nonclustered PRIMARY KEY on that identity.

OK, now I have solved the problem by an approach I found at some other post
presented by Joe Celko:

CREATE TABLE Foobar

(id INTEGER NOT NULL PRIMARY KEY,

name CHAR(10) NOT NULL);

INSERT INTO Foobar VALUES (12, 'Johnson');

INSERT INTO Foobar VALUES (57, 'Nelson');

INSERT INTO Foobar VALUES (11, 'Roberts');

INSERT INTO Foobar VALUES (68, 'Smith');

SELECT F1.id, F1.name,

(SELECT MAX (id)

FROM Foobar AS F2

WHERE F2.id < F1.id) AS prev_id,

(SELECT MIN(id)

FROM Foobar AS F3

WHERE F3.id > F1.id) AS next_id

FROM Foobar AS F1;

These subquery expressions are the LUB (least upper
bound) and the GLB (greatest lower bound).

Greetings everyone!

Is there a way to stop query analyzer from processing remaining query
statements? Let's say i have the following query in query analyzer:

query statements
if condition
begin
query statements
stop the query
end

query statements
if condition
begin
query statements
stop the query
end

is there a way to stop the query from executing the statements in the
second query when the first condition is met?

i have searched BOL and google but i couldn't find anything. I hope
anybody out there can help me.

create from dbo.fromExample
as
from dbo.Customers c
join dbo.Invoices i
on c.CustomerID = i.CustomerID

now you can use it as follows:

select c.CustomerID, i.InvoiceNo
from dbo.fromExample

I find I reuse FROM clauses alot

and reusing a "base query" requires writing a very large and cumbersome
select clause

In message 127.0.0.1>, Erland Sommarskog
writes

Erland makes a good point. The essence of any argument about
upgrading is that eventually they will have to upgrade, either to SQL
2005 or some other version. The question is: do they want to be
proactive, and take time to plan it, or do they want to be reactive,
and have their schedule for upgrading dictated by market forces.

None of our vendors that supply SQL Server products require SQL 2005,
but I'm betting that new releases this year will begin requiring SS2005
databases. In other words, our current apps don't require 2005, but
future versions may. I'd rather start planning for that contingency
now than be forced into it by a necessary patch for a vendor software.

I'm not saying that upgrades are the only option; could always buy a
new server for applications that require SS2005.

Stu

Erland Sommarskog wrote:

Nuwan K (ckid@hotmail.com) writes:

6½ to be precise. :-)

To be perfectly honest, I don't think is a very good argument for
upgrading to SQL 2005. It rather makes the argument "we can be on all
this update trains, so we wait for the next one". And Microsoft has
indicated that from now, it's a two-year release cycle for SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

(cabrenner@optonline.net) writes:

So a file could look like this:

2,34,Enter Sandman,Pat Boone
9,34,Zabadak,"Dave, Dee, Dozy, Mich & Tich"
8,981,"Rebel, Rebel",David Bowie

Erland Sommarskog wrote:

Thanks Erland.