February 26, 2008 at 5:59 am
I can not for the life of me find my notes on this.
Consider the following tables:
TABLE person
(
pid IDENTITY(1, 1) INT,
pname varchar (150)
)
TABLE shoes
(
sid IDENTITY(1, 1) INT,
pid INT,
sname varchar (150)
)
A person can have many shoes. I want to return the data in a report like this:
Joe | nike, timberland, sneakers, flip flop
Bob | nike, timberland
I know I "could" use a cursor but I remember there is a pure SQL way to do this. Something like:
-- Create a temp table of people with a column for shoes
SELECT pid, pname, CAST('' AS varchar (700)) as shoelist into #tmp from person
UPDATE T SET T.shoelist = T.shoelist + S.sname + ',' FROM
#tmp T INNER JOIN shoes S ON T.pid = S.pid ORDER BY S.sname
But that doesn't seem to work. I can do it with a variable a la:
DECLARE @txt varchar (1000)
SET @txt = ''
SELECT @txt = CASE WHEN LEN(@txt) = 0 THEN col1 ELSE
@txt + ', ' + col1 END FROM myTable
How can I do this with a table only solution, or can I?
Thanks
ST
February 26, 2008 at 6:14 am
February 27, 2008 at 4:24 am
I understand the concept of a cross-tab query, however, I don't see how it can be applied in a case where you don't know the number of related records. It needs to be dynamic.
Thanks
ST
March 4, 2008 at 5:32 am
Bump
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply