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

    Unanswered: names and alternate names

    Ok, I have to brought up this again... the last query (refer to my previous topic, 'Put all data in one field but only limit to 2?') 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!

  2. #2
    Join Date
    Jan 2009
    Posts
    17
    declare @t table(name varchar(10),address varchar(10), phone varchar(15))
    insert into @t
    select 'company a','address a','111-1111' UNION all
    select 'company b','address a','111-1111' UNION all
    select 'company c','address a','111-1111' UNION all
    select 'company c','address b','111-1111' UNION all
    select 'company c','address b','111-1111' UNION all
    select 'company c','address b','111-1111'

    select max(case when rn=1 then name end) as 'name',
    address,phone,
    max(case when rn=2 then name end) +','+max(case when rn=3 then name end) as 'alname'
    from
    (
    select name,row_number() over (partition by address order by name) as 'rn',address,phone from @t
    )as t
    group by address,phone

  3. #3
    Join Date
    Jun 2008
    Posts
    49
    Ah, sorry. Forgot to tell that the row_number() won't work on me coz I'm using ms sql 2000

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    Code:
    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

Posting Permissions

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