April 3, 2009 at 12:36 pm
Hi David
This is quiet similar to another post:
http://www.sqlservercentral.com/Forums/Topic686470-338-1.aspx
There you can also find a very good solution from Bob Hovious.
Greets
Flo
April 4, 2009 at 11:54 am
All,
Thanks for your input, I'll test out all of the suggestions and get back to you but it may take a little time!
April 6, 2009 at 3:28 pm
Just wanted to let you know that after a bit of fighting with it, I was successful in using a UDF to put the data in the format necessary, and used that in my master query to retrieve the data as was needed. Thanks again for your assistance!
Linda
Vijaya Kadiyala (4/2/2009)
HiCheck out the below link this will solve your problem.
April 7, 2009 at 2:22 am
Linda hammell (4/6/2009)
Just wanted to let you know that after a bit of fighting with it, I was successful in using a UDF to put the data in the format necessary, and used that in my master query to retrieve the data as was needed. Thanks again for your assistance!Linda
Vijaya Kadiyala (4/2/2009)
HiCheck out the below link this will solve your problem.
Hi Linda,
Any chance I could have a look at the code you produced please? 😉
April 7, 2009 at 9:05 am
I had to mask the actual column and table names for proprietary reasons and the actual query is much more involved then the little portion I'm sharing here, but hopefully it will give you some idea of what to do.
This is the function where I have several rows that have the same information, except for column1 which has different information in each row. So basically I wanted it to pull back the column1 information, and put a comma in between each item it returned. When it found all items, that whole comma separated list I wanted return to the main query as a single item called "System Name". It had to read the information in table1 which was just an ID, find the actual text name that that ID refers to from table2, then do this again for each item it found in column1 that matched the original item. When nothing else was found, it returned the whole list as one field to the main query.
ALTER Function [dbo].[GetSystemName]
(
@SysID varchar(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @s varchar(8000)
SELECT @s = ISNULL(@s+' , ','') + table1.column1
FROM table1 INNER JOIN
table2 ON table2.sys_ID = table1.sys_ID
WHERE (table1.syslistId = '1234') AND
table2.syslistid=@sysID
Return @s
End
The main query grabs the information from a table, and the field returned from the function, which it names "System".
A.tableid as 'SYS_ID',
dbo.GetSystemName(a.tableid) As System // This returns the comma separated field
from
table1 A
where
A.syslistId = '1234'
Linda
David (4/7/2009)
Hi Linda,
Any chance I could have a look at the code you produced please? 😉
April 7, 2009 at 9:30 am
Linda hammell (4/6/2009)
Just wanted to let you know that after a bit of fighting with it, I was successful in using a UDF to put the data in the format necessary, and used that in my master query to retrieve the data as was needed. Thanks again for your assistance!Linda
Vijaya Kadiyala (4/2/2009)
HiCheck out the below link this will solve your problem.
Finally my solution is working 🙂
April 8, 2009 at 11:57 am
Florian Reischl (4/3/2009)
Hi DavidThis is quiet similar to another post:
http://www.sqlservercentral.com/Forums/Topic686470-338-1.aspx
There you can also find a very good solution from Bob Hovious.
Greets
Flo
Flo, I managed to do it by following Bob's method! It's not pretty because I have to do grouping within the CTE and the x and y joined tables BUT it does give me exactly what I want!
Thanks for the link and thanks to everyone else for their patience!
April 8, 2009 at 12:55 pm
David (4/8/2009)
Florian Reischl (4/3/2009)
Hi DavidThis is quiet similar to another post:
http://www.sqlservercentral.com/Forums/Topic686470-338-1.aspx
There you can also find a very good solution from Bob Hovious.
Greets
Flo
Flo, I managed to do it by following Bob's method! It's not pretty because I have to do grouping within the CTE and the x and y joined tables BUT it does give me exactly what I want!
Thanks for the link and thanks to everyone else for their patience!
You are very welcome!
Glad to help you 🙂
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply