Make a Bunch of Rows from a Table Resemble a Column

[laurenq uantrell]

Currently I'm using a UDF and a physical temp table to accomplish this
and I want to know if there's any way of doing it in a query or
subquery instead...

In TableA there are a bunch of rows:

InvitationID (PK) PartyID Partygoer
1 1 Jim
2 20 Bob
3 2 Frank
4 1 Robert
5 1 Pete

In TableB are rows:
PartyID (PK) PartyDate PartyName
1 1/1/2000 WildParty
2 1/1/2000 BoringParty

When a user runs a query to search for all parties on 1/1/2000 I want
the result to look like:

PartyID PartyDate PartyName Partygoer
1 1/1/2000 WildParty Jim, Robert, Pete
2 1/1/2000 BoringParty Frank
I'm hoping there's a solution to this.
Thanks,
lq

[Razvan Socol]

See: http://www.aspfaq.com/show.asp?id=2529

Razvan

[laurenq uantrell]

Razvan
Thanks for that example. However, both the solutions outlined use a
temp table and a custom function, both of which I said I am already
using and trying to figure out a way without...

"Currently I'm using a UDF and a physical temp table to accomplish this

and I want to know if there's any way of doing it in a query or
subquery instead... "

lq

[--CELKO--]

So you are one of the kids that missed the part about 1NF in your RDBMS
class in college. Google it.

This is a report and you do it in the front end. But you can look up
some kludges that do not use Standard SQL.

[Tony Rogerson]

> But you can look up

some kludges that do not use Standard SQL.
Or you can hire a consultant that deals with pure standard SQL, roughly
speaking, somebody that will cost more, not give you an answer to your
business problem and is more interested in there own indulgence.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11********************@g14g2000cwa.googlegrou ps.com... So you are one of the kids that missed the part about 1NF in your RDBMS
class in college. Google it.

This is a report and you do it in the front end. But you can look up
some kludges that do not use Standard SQL.

[Tony Rogerson]

You could use a UDF and cursor, the UDF would be called on the SELECT clause
so all the up-front accessing would be efficient and just the resulting rows
would require the resulting slow UDF code.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Currently I'm using a UDF and a physical temp table to accomplish this
and I want to know if there's any way of doing it in a query or
subquery instead...

In TableA there are a bunch of rows:

InvitationID (PK) PartyID Partygoer
1 1 Jim
2 20 Bob
3 2 Frank
4 1 Robert
5 1 Pete

In TableB are rows:
PartyID (PK) PartyDate PartyName
1 1/1/2000 WildParty
2 1/1/2000 BoringParty

When a user runs a query to search for all parties on 1/1/2000 I want
the result to look like:

PartyID PartyDate PartyName Partygoer
1 1/1/2000 WildParty Jim, Robert, Pete
2 1/1/2000 BoringParty Frank
I'm hoping there's a solution to this.
Thanks,
lq

[Alexander Kuznetsov]

> This is a report and you do it in the front end.

Why?
Doing it at the back end has some advatages too:
1. Less data is transferred via the network. Since data is sent in
packets, 100 bytes less can easily mean 1 packet instead of 2 - 100%
better performance. In some cases that's crucial.
2. When (I'm not saying IF, but WHEN) the front end is rewritten, this
functionality is still there.
If several different front ends access one and the same query, there is
no redundancy either

[Erland Sommarskog]

laurenq uantrell (la*************@hotmail.com) writes:

Currently I'm using a UDF and a physical temp table to accomplish this
and I want to know if there's any way of doing it in a query or
subquery instead...

On SQL 2000, not really. (Unless you want to rely on undefined behaviour.)

On SQL 2005, there is actually away. Here I include only a canned example
that I got from one of the SQL Server devs:

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

It's quite appalling, and I still have not learn enough XQuery to
understand how this works - only that it works.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.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

[laurenq uantrell]

Tony,
I have a very workable solution that uses only a UDF (no cursor and no
temp table) and seems pretty efficient on a table with approx 100,000
rows. I'm just wondering if there''s a way to do it in TSQL.

[laurenq uantrell]

Yeah, I caught that feature addition in 2005 but since the client here
is running SQL 2K I didn't bother trying to figure it out.
Thanks for the reply.
lq