Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2008
    Posts
    49

    Unanswered: Put all data in one field but only limit to 2?

    I once ask for help here about getting all the names and put all in one field for records with duplicate phone numbers... ex:

    HTML Code:
    <table>
    <td>name</td>            <td>address</td>             <td>phone</td>
    <tr>
    <td>company a</td>    <td>address a</td>          <td>111-1111</td>
    <td>company b</td>    <td>address a</td>          <td>111-1111</td>
    <td>company c</td>    <td>address a</td>          <td>111-1111</td>
    </table>

    and the result:

    phone names
    111-1111 company a, company b, company c


    This is the function given to me:

    ALTER FUNCTION dbo.concat_companies (@telephone_number varchar(30), @delimiter varchar(5))
    RETURNS varchar(300)
    BEGIN


    DECLARE @companies varchar(300)

    SELECT @companies = Coalesce(@companies, '') + businessname + @delimiter
    FROM temptable
    WHERE phone = @telephone_number
    GROUP
    BY businessname
    -- SET @companies = Left(@companies, Len(@companies) - DataLength(@delimiter))

    RETURN @companies
    END
    I tried to find my first thread about this but couldn't find it, guess its already gone. Can't recall anymore the one who help me with this and again I thank you for that.

    Now, I'm adjusting the function. I think its simple but I just don't get it... I need to limit the names that are put in one field.. so, for example, I only need 2 names, nothing more:

    name address phone
    company a address a 111-1111
    company b address a 111-1111
    company c address a 111-1111


    and the result will be just like this:

    phone names
    111-1111 company a, company b <- only 2 names. company c and other succeeding names will not be shown...


    I hope you understand what I meant.. could somebody gave me an idea...?
    Last edited by BlueGemini; 03-03-09 at 04:57.

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    try this once
    declare @tab table(name varchar(32), address varchar(32), phone varchar(32))
    insert into @tab select 'company a', 'address a', '111-1111'
    insert into @tab select 'company b', 'address a', '111-1111'
    insert into @tab select 'company c', 'address a', '111-1111'

    select identity(int,1,1)as rid, * into #temp from @Tab

    select stuff(( Select ','+name
    from
    (Select address,phone,name,(Select Count(*) from #temp
    Where phone = Z.phone and rid <= Z.rid ) as Seq
    from #temp Z)M
    where seq <=2 for xml path('')),1,1,'')

    drop table #temp

  3. #3
    Join Date
    Jun 2008
    Posts
    49
    I'm having error like this:

    Server: Msg 170, Level 15, State 1, Line 13
    Line 13: Incorrect syntax near 'xml'.

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    r u using sql 2000 version or 2005
    xml path will works from 2005 onwards

  5. #5
    Join Date
    Jun 2008
    Posts
    49
    Oh sorry... I forgot to mention. I'm using sql server 2000..

  6. #6
    Join Date
    Dec 2008
    Posts
    135
    try this just used ur function
    declare @tab table(name varchar(32), address varchar(32), phone varchar(32))
    insert into @tab select 'company a', 'address a', '111-1111'
    insert into @tab select 'company b', 'address a', '111-1111'
    insert into @tab select 'company c', 'address a', '111-1111'

    select identity(int,1,1)as rid, * into #temp from @Tab

    Select address,phone,name,(Select Count(*) from #temp
    Where phone = Z.phone and rid <= Z.rid ) as Seq into testdata
    from #temp Z

    CREATE FUNCTION dbo.concat_companies (@telephone_number varchar(30), @delimiter varchar(5))
    RETURNS varchar(300)
    BEGIN

    DECLARE @companies varchar(300)

    SELECT @companies = Coalesce(@companies, '') + @delimiter + name
    FROM testdata
    WHERE phone = @telephone_number
    and seq <= 2
    GROUP
    BY name
    -- SET @companies = Left(@companies, Len(@companies) - DataLength(@delimiter))

    RETURN @companies
    END

    select dbo.concat_companies('111-1111',',')

    drop table #temp
    drop table testdata
    drop function concat_companies

    r use while loop if ur not intrested using function

  7. #7
    Join Date
    Jun 2008
    Posts
    49
    I just tried it out. Seems okay..

    Thank you very much!

  8. #8
    Join Date
    Jun 2008
    Posts
    49
    Ok, I have to brought up this again... the last query really helped me (Thanks bklr) now this is another task I must do... this is almost the same as the first one I ask but this time I mustn't include the first name of a record in the field where all the names with the same phone number are put into... example:

    I have this table
    name address phone
    ----------------------------------------------------------------------------
    company a address a 111-1111
    company b address a 111-1111
    company c address a 111-1111

    Now I must make a result like this...
    name | address | phone | alternate names
    ------------------------------------------------------------------------------
    company a | address a | 111-1111 | company b, company c

    So, basically, 'company a' is the primary name for that record and the rest of the names with the same phone number with be place in the 'alternate names' field.

    Something like that. I hope you get it.



    Thanks!

  9. #9
    Join Date
    Dec 2008
    Posts
    135
    code
    try this gemini,
    ALTER FUNCTION dbo.concat_companies (@telephone_number varchar(30), @delimiter varchar(5))
    RETURNS varchar(300)
    BEGIN

    DECLARE @companies varchar(300)

    SELECT @companies = Coalesce(@companies, '') + @delimiter + name
    FROM testdata
    WHERE phone = @telephone_number
    and seq >1 and seq<=3
    GROUP
    BY name
    RETURN @companies
    END

    select name, address, phone,(select dbo.concat_companies('111-1111',','))as alternatenames from testdata
    where seq = 1

    drop function dbo.concat_companies

  10. #10
    Join Date
    Jun 2008
    Posts
    49
    Just check this...

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •