Urgent help required please! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Urgent help required please!

Author

Topic

keentolearn
Starting Member

21 Posts

Posted - 2009-07-29 : 08:38:49

I am extracting large volume of historic data into excel to archive. Because of the multi value fields like Team, too many rows are returned for each unique record. So I wanted to create a user defined function to get all team names for a record into one cell and seperate them with ;
But my query doesn't return the correct team for the records.
My function is:
/*
Function to output a list of team members for a given List
List is inputted as a ROW_ID parameter

usage: select c1.ffcode_desc as Team_Members, dbo.team(d.ffitem_id) as team from ffdocument d

*/
CREATE FUNCTION [Lteam]
-- input parameter
(@list_row_id varchar(50))
RETURNS varchar(1000) AS
begin

declare @Lteam_member varchar(256)
declare @Lteam_string varchar(256)
-- sql query for cursor
declare csr_Lteam cursor for
select c.ffcode_desc
from ffcodelist as c
inner join ffrelation as r on c.ffitem_id = r.ffitem_id2
inner join ffdocument as d on (r.ffitem_id1 = d.ffitem_id and r.ffoption = 'TEAM_MEMBE')
where r.ffitem_id1 = @list_row_id

-- start with an empty team list
set @Lteam_string = ''

-- open the cursor and iterate through the list of team members for client
open csr_Lteam
fetch next from csr_Lteam into @Lteam_member
while @@fetch_status = 0
begin
-- build the list and insert a semicolon
set @Lteam_string = @Lteam_string + @Lteam_member + '; '

fetch next from csr_Lteam into @Lteam_member
end
close csr_Lteam
deallocate csr_Lteam

-- remove any white space at the front and end of the string
set @Lteam_string = ltrim(rtrim(@Lteam_string))

-- if there is a comma at the end then remove it
if right(@Lteam_string, 1) = ';'
set @Lteam_string = left(@Lteam_string, len(@Lteam_string) - 1)

-- if there weren't any team members then set the variable to null
if @Lteam_string = ''
set @Lteam_string = null

-- return the list of team members for pitch
return @Lteam_string
end

when i run my query using the dbo.lteam I don't get the right results:

select d.ffitem_id, dbo.lteam(r.ffitem_id1) as team,
from ffcodelist as c
inner join ffrelation as r on c.ffitem_id = r.ffitem_id2
inner join ffdocument as d on (r.ffitem_id1 = d.ffitem_id and r.ffoption = 'TEAM_MEMBE')

but when I run it without the dbo.lteam :
select d.ffitem_id, c1.ffcode_desc from ffdocument d
left outer join ffrelation as r1 on (r1.ffitem_id1 = d.ffitem_id and r1.ffoption = 'TEAM_MEMBE')
left outer join ffcodelist as c1 on c1.ffitem_id = r1.ffitem_id2

I get the correct results so there must be something wrong with my function.

Many thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 08:40:51

see concatenate records without UDF

KH
[spoiler]Time is always against us[/spoiler]

keentolearn
Starting Member

21 Posts

Posted - 2009-07-29 : 11:39:52

I think i need UDF as my my team values are fom a different table which is link to my document table for the itemIDs. e.g. for each distinct itemID in my document table I need to get the team names concatenated into one cell from a table called codelist, i need to use relation table to link document table to the codelist table. e.g.

select d.ffitem_id, c1.ffcode_desc from ffdocument d
left outer join ffrelation as r1 on (r1.ffitem_id1 = d.ffitem_id and r1.ffoption = 'TEAM_MEMBE')
left outer join ffcodelist as c1 on c1.ffitem_id = r1.ffitem_id2

this returns the correct team members but returns multiple rows for each itemID.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-29 : 11:46:00

You can try it this way...with a table declaration.

DECLARE  @t  TABLE( 
                   ffitem_id   INT, 
                   ffcode_desc VARCHAR(MAX) 
                   ) 

INSERT @t 
SELECT d.ffitem_id, 
       c1.ffcode_desc 
FROM   ffdocument d 
       LEFT OUTER JOIN ffrelation AS r1 
         ON (r1.ffitem_id1 = d.ffitem_id 
             AND r1.ffoption = 'TEAM_MEMBE') 
       LEFT OUTER JOIN ffcodelist AS c1 
         ON c1.ffitem_id = r1.ffitem_id2 

SELECT   DISTINCT s1.ffitem_id, 
                  STUFF((SELECT ',' + s2.ffcode_desc 
                         FROM   @t AS s2 
                         WHERE  s2.ffitem_id = s1.ffitem_id 
                         FOR XML PATH('')),1,1,'') AS ffcode_desc 
FROM     @t AS s1 
ORDER BY s1.ffitem_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources