By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,259 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,259 IT Pros & Developers. It's quick & easy.

Make a Bunch of Rows from a Table Resemble a Column

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a

P: n/a
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

Nov 23 '05 #3

P: n/a
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.

Nov 23 '05 #4

P: n/a
> 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.

Nov 23 '05 #5

P: n/a
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

Nov 23 '05 #6

P: n/a
> 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

Nov 23 '05 #7

P: n/a
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
Nov 23 '05 #8

P: n/a
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.

Nov 23 '05 #9

P: n/a
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

Nov 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.