locked
SQL query ... RRS feed

  • Question

  • User-22218653 posted

    table1

    Name id

    a 12

    b 10

    table2 

     refID Name

    12 qq

    12 cc

    12 dd

    10 rr 

     

    how can i made a sql query to do the following output

    Name Id otherWords 

    a 12 qq,cc,dd

    b 10  rr

     

    Monday, April 30, 2007 2:05 AM

Answers

  • User-1786411686 posted
    Cursor is not required. Here's a sample: This method could be a little bit slower if you are using this function on too many rows ( > few thousands of rows) because the function has to be evaluated for each row.
     
     
    create table table2  ( refID int , Name varchar(10))
    insert into table2 select 12, 'qq' union all
    select 12, 'cc' union all
    select 12, 'dd' union all
    select 10, 'rr'
    
    alter function dbo.fn_GetOtherWords( @id int)
    returns varchar(100)
    as
    begin
    	
    declare @otherwords varchar(100)	
    select @otherwords = isnull(@otherwords,'') + ',' + name from table2 where refid = @id
    
    return right(@otherwords, len(@Otherwords) - 1)
    end
    go
    
    
    declare @table1 table (Name varchar(10), id int)
    
    insert into @table1 select 'a', 12 union all
    select 'b', 10
    
    select name, id, dbo.fn_GetOtherWords(id)
    from @table1
    
    
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 30, 2007 11:52 AM

All replies

  • User-319574463 posted
    This is a case where a cursor is almost inevitable. I will write one for later (if not one else posts the answer sooner).
    Monday, April 30, 2007 7:33 AM
  • User-1786411686 posted
    Cursor is not required. Here's a sample: This method could be a little bit slower if you are using this function on too many rows ( > few thousands of rows) because the function has to be evaluated for each row.
     
     
    create table table2  ( refID int , Name varchar(10))
    insert into table2 select 12, 'qq' union all
    select 12, 'cc' union all
    select 12, 'dd' union all
    select 10, 'rr'
    
    alter function dbo.fn_GetOtherWords( @id int)
    returns varchar(100)
    as
    begin
    	
    declare @otherwords varchar(100)	
    select @otherwords = isnull(@otherwords,'') + ',' + name from table2 where refid = @id
    
    return right(@otherwords, len(@Otherwords) - 1)
    end
    go
    
    
    declare @table1 table (Name varchar(10), id int)
    
    insert into @table1 select 'a', 12 union all
    select 'b', 10
    
    select name, id, dbo.fn_GetOtherWords(id)
    from @table1
    
    
    
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 30, 2007 11:52 AM
  • User-22218653 posted

    Thanks ndinakar :-) ...

    it helps me a lot..... hope we will get more and nice solution in the future .... from people like u. 

    Wednesday, May 2, 2007 1:26 AM