Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Location
    India
    Posts
    96

    Unanswered: Two or more row values in a single row

    Guys,

    I have one senario.

    Create table Client_Table
    (
    ID int,
    SUB int,
    Name varchar(30),
    Status varchar(10)
    )
    go

    insert into Client_Table
    select 9001,4,'Gail','Active'
    Select 9001,1,'Mark','Active'
    union all
    select 9001,2,'Will','InActive'
    union all
    select 9001,4,'Gail','Active'
    union all
    select 9002,1,'chals','Active'
    union all
    select 9003,1,'ken','Active'
    union all
    select 9003,2,'dave','Active'
    union all
    select 9004,1,'peter','Active'
    union all
    select 9004,2,'Jack','InActive'

    each ID can have any no of clients,

    i need to select only the first two Active clients for each ID in a single row

    Example Output

    ID Name1 Name2
    9001 Mark steve
    9002 chals
    9003 Ken dave
    9004 Peter

  2. #2
    Join Date
    Jun 2008
    Location
    India
    Posts
    96
    Any inputs ?

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    No easy way as you have seen from
    http://www.dbforums.com/showthread.php?t=1630984

    You can do it with a self join
    or a temp table with a new identity
    or a cursor

    Your expected output say
    9001 Mark steve
    but is not part of your test data I guess it was a copy paste error
    test data should have
    select 9001,3,'steve','Active'

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Here is code for the self join. It should be OK if you don't have huge volumes.
    Code:
    select c.ID
    ,name1=min(case when c.SUB=e.ms1 then c.Name end)
    ,name2=isnull(min(case when c.SUB=e.ms2 then c.Name end),'')
    from   Client_Table c
    join (
      select a.ID, ms1=min(a.ms1), ms2=min(b.SUB)
      from   (
        select ID, ms1=min(SUB)
        from   Client_Table 
        where  Status='Active'
        group by ID) a 
      left join Client_Table b
         on a.ID=b.ID
        and b.Status='Active' 
        and (b.SUB>a.ms1 or b.SUB is null)
      group by a.ID) e 
    on  e.ID=c.ID
    and c.SUB in (e.ms1,e.ms2)
    where c.Status='Active'
    group by c.ID

Posting Permissions

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