I'm sure this has been brought up many times, but I will ask anyway.

Let's say I have 2 tables related:

Owner:
---------
o_id
o_name

Dog:
---------
d_id
d_name
o_id - for Owner table.

If the data is laid out as

o_id o_name
1 John

d_id d_name o_id
1 Skippy 1
2 Fido 1

How can I make a query that will produce the following results:

o_id o_name owned dog names
1 John Skippy, Fido

I think it has something to do with unions but I can't seem to get it. I'm
using SQL Server Compact Edition.

Steve London (sylondon (AT) optonline (DOT) net) writes:
There isn't any straight-forward way of doing this. It is possible do this
one statement with a quirk of XML as in this example:

select CustomerID,
substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1)
-- strip the last ',' from the list
from
Customers c cross apply
(select convert(nvarchar(30), OrderID) + ',' as [text()]
from Orders o
where o.CustomerID = c.CustomerID
order by o.OrderID
for xml path('')) as Dummy(OrdIdList)
go

I don't know if all this syntax is available, in Compact Edition, though.
(While called SQL Server, it's an entirely different engine.)

Else you will have run a cursor to achieve this result.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

I had to work with the Compact Edition recently and it supports really a
limited set of T-SQL. There are no stored procedures, user functions,
control of flow constructs (like IF..ELSE, but CASE is available), views,
triggers, and cursors. None of the new ranking functions for SQL 2005 are
available as well as the XML specific handling (like FOR XML). Also, CROSS
APPLY is not supported, and no DECLARE for variables...

It is really "compact" and to keep it that way it has only the basic query
support of the database engine (and I mean really basic - I could not even
use derived tables in FROM and subqueries in the SELECT list). For all other
functionality it depends on the rich functionality of ADO.NET and/or your
application layer. So, probably the best way to approach this problem is to
solve it in the application logic.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Yea, I already do it in the Application Layer. It was just a passing fancy
to remove some code and let SQL do it.

I think I sent you an email accidently. Just delete it. Sorry about that.
Windows Mail is so stupid. I really need to switch.

"Plamen Ratchev" <Plamen (AT) SQLStudio (DOT) com> wrote

Plamen Ratchev (Plamen (AT) SQLStudio (DOT) com) writes:
But, it has at least one thing big brother does not have: ALTER TABLE
syntax to add/remove the IDENTITY property. I find it amazing.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Good catch!

I guess it makes sense to give a little more power in some areas to
compensate for others. There are some sides of the Compact Edition that make
it very attractive (in particular situations). I like that it has a very
small footprint (only 7 DLLs) and it can be installed via copying the DLLs
to the application directory (that way no administrative privileges
required). Also, supports the ADO.NET Sync Framework, allows to have the
database file on a network share, and to some degree higher safety (since it
does not support T-SQL procedural code). And if the OLE DB provider is used
then no need to install the .NET 2.0 Framework. So, a good addition to the
family...

Plamen Ratchev
http://www.SQLStudio.com

Yes it is brought up all the time because people will not bother to
read even one book on RDBMS, so they keep asking this kind of thing
over and over. Please post DDL, so that people do not have to guess
what the keys, constraints, Declarative Referential Integrity, data
types, etc. in your schema are. Let's start by doing what you should
have done for us:

CREATE TABLE Owners -- plural if you have more than one
(owner_id INTEGER NOT NULL PRIMARY KEY,
owner_name CHAR(20) NOT NULL);

CREATE TABLE Dogs -- plural if you have more than one
(dog_id INTEGER NOT NULL PRIMARY KEY,
dog_name CHAR(20) NOT NULL);

Why did you think that an owner is an attribute of a dog? It is a
relationship! It might have attributes of its own, like license
numbers, issue date, etc. but let's ignore that.

CREATE TABLE Ownership-- plural if you have more than one
(owner_id INTEGER NOT NULL
REFERENCES Owners(owner_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
dog_id INTEGER NOT NULL
REFERENCES Dogs(dog_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (dog_id, owner_id));

Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? It is the foundation of RDBMS, after all. See why I
say you never read a book on RDBMS.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Yes, trhre are some proprietary, stinking kludges that can do this.
But do you want to be a good SQL programmer instead?

No, I want to be the worst programmer on the planet. I mean really.

Oh, I'm sorry about that. I failed to get a copy of the CELKO SQL posting
etiquette. Could you please point me in the right direction?
Sarcasm aside, I will do this in the future. Thanks.

You're right I should have went out and bought a book on RDBMS. I mean who
would ever think about asking a question on the internet was possible. I
mean everybody should shut down all forums and rely on tech manuals because
they are always so well written and contain every piece of knowledge on the
planet about the subject, including all the hidden tips and tricks.
Questions will be a thing of the past. Just grab a book and your answer
will be there. Did you get criticized in class when you had a question
about something? Did the teacher yell at you, tell you to read the book and
never answer the question?

Since, my application is small and does very basic SQL usage, the internet
provided all the information I needed, but a book would have answered my
question so the might CELKO would not have been bothered. I guess I figured
there are some decent people out there that would be willing to help out
somebody new to the RDBMS world, thank you proving me wrong. Others, seem
just fine. Maybe you need to relax and not get so worked up over a
question. I mean if it bothers you that much you might want to take a
vacation and relax buddy before your next heart attack.

Thank you for all you criticism and posting how I failed to put license
numbers, issue date, and any other things for you. I tried to make it small
and simple for the example. I guess for CELKO it was a very, very poor
example.

I think a dog belongs to an owner. Can an owner have more than 1 dog?
Hmmm.... I think so in this example. Yes, creating a seperate table in this
example to handle 1 to many is politically correct but I wasn't asking to be
critiqued on correctness but for a simple answer to a simple question. I
already handle this in my application but it was a passing fancy to see if
it was possible to do in SQL to better my knowledge. Thanks for not
anwering my question and making a noob to RDBMS feel welcome. I appreciate
it!!!

Maybe, the great CELKO can recommend a book and help out a newly RDBMS
programmer into this world instead of criticising each and every word and
never help out except to say how bad and wrong the post was.

Steve London (Yuri)


"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

On Mar 9, 3:43 am, "Steve London" <sylon... (AT) optonline (DOT) net> wrote:
hi,
Following code will work but you might have to customize it to fit
your requirement.

declare @table table (o_id int, o_name varchar(50))
declare @dog table (d_id int, d_name varchar(50),o_id int)
declare @owner_dogs table(o_id int, o_name varchar(50), d_name
varchar(50))
insert @table values (1,'John')
insert @dog values(1,'Skippy',1)
insert @dog values(2,'Fido',1)

declare @dog_name varchar(50)

while (select count(*) from @dog) > 0
begin
set rowcount 1
if @dog_name is null
begin
select @dog_name = d_name
from @dog
where o_id = 1
end
else
select @dog_name = @dog_name + ', ' + d_name
from @dog
where o_id = 1

delete @dog
end

insert @owner_dogs values(1,'John',@dog_name)
select * from @owner_dogs
set rowcount 0

On Mar 12, 2:43 pm, othell... (AT) yahoo (DOT) com wrote:
Hi,
I just read your complete post and realized that you might not be able
to get it working. So here is the complete code:

declare @table table (o_id int, o_name varchar(50))
declare @dog table (d_id int, d_name varchar(50),o_id int)
declare @owner_dogs table(o_id int, o_name varchar(50), d_name
varchar(50))

declare @dog_temp table (d_id int, d_name varchar(50),o_id int)
declare @id table (o_id int)

insert @table values (1,'John')
insert @table values (2,'Mary')

insert @dog values(1,'Skippy',1)
insert @dog values(2,'Fido',1)
insert @dog values(3,'Ralph',2)
insert @dog values(4,'Alf',2)

declare @dog_name varchar(50)
declare @o_id int

insert @id
select distinct o_id
from @table

select @o_id = max(o_id) from @id
--select top 1 @o_id = o_id from @id

while @o_id is not null
begin
delete @id
where o_id = @o_id

insert @dog_temp
select *
from @dog
where o_id = @o_id

select @dog_name = null
while (select count(*) from @dog_temp) > 0
begin
set rowcount 1
if @dog_name is null
begin
select @dog_name = d_name
from @dog_temp
where o_id = @o_id
end
else
select @dog_name = @dog_name + ', ' + d_name
from @dog_temp
where o_id = @o_id

delete @dog_temp
end

insert @owner_dogs (o_id,d_name)
values (@o_id,@dog_name)

set rowcount 0

select @o_id = max(o_id) from @id
end


update @owner_dogs
set o_name = a.o_name
from @table a,
@owner_dogs b
where b.o_id = a.o_id

select * from @owner_dogs