|
Home > Archive > Microsoft SQL Server forum > November 2005 > Make a Bunch of Rows from a Table Resemble a Column
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Make a Bunch of Rows from a Table Resemble a Column
|
|
| laurenq uantrell 2005-11-17, 8:24 pm |
| 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
| |
|
|
| laurenq uantrell 2005-11-19, 11:23 am |
| 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-- 2005-11-19, 1:23 pm |
| 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 2005-11-19, 8:23 pm |
| > 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--" <jcelko212@earthlink.net> wrote in message
news:1132423662.438219.4440@g14g2000cwa.googlegroups.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 2005-11-19, 8:23 pm |
| 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" < laurenquantrell@hotm
ail.com> wrote in message
news:1132268951.082186.155340@g44g2000cwa.googlegroups.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 2005-11-20, 8:23 pm |
| > 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 2005-11-20, 8:23 pm |
| laurenq uantrell (laurenquantrell@hot
mail.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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
| |
| laurenq uantrell 2005-11-21, 7:23 am |
| 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 2005-11-21, 7:23 am |
| 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
|
|
|
|
|