August 11, 2008 at 1:06 pm
hi gems
i have the following issue of selecting multiple row values in a single row.
example data
client table data
ID SUB NAME Status
9001 1 Mark Active
9001 2 Will InActive
9001 3 steve Active
9002 1 chals Active
9003 1 ken Active
9003 2 dave Active
9004 1 peter Active
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
i spent a lot of time on this but was not able to.....
please help me out in solving this....thanks in advance
August 12, 2008 at 5:40 am
Sudheer,
Try it out.
Create table Client_Table
(
ID int,
SUB int,
Name varchar(30),
Status varchar(10)
)
go
insert into Client_Table
Select 9001,1,'Mark','Active'
union all
select 9001,2,'Will','InActive'
union all
select 9001,3,'steve','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'
select ID,Name,Seq = IDENTITY(int,1,1),'Name1' = convert(varchar(25),''),'Name2' = convert(varchar(25),'')
into #t1
from Client_Table
where Name in ( select Name from Client_Table B where Client_Table.ID = B.ID and B.Status ='Active')
Update #t1
set Name1 = isnull(( select Name
from #t1 a
where a.ID = #t1.ID and a.Seq in ( select min(Seq) from #t1 group by ID ) ),'')
Update #t1
set Name2 = isnull(( select Name
from #t1 a
where a.ID = #t1.ID and a.Seq in ( select max(Seq) from #t1 group by ID having count(*) > 1 ) ),'')
select distinct ID,Name1,Name2 from #t1
Output:
9001Marksteve
9002chals
9003kendave
9004peter
Note :
But i didn't perform any performance test. I think my code may comes under 'Hidden RBAR' concept.
I hopefully expect suggestions from good experts.
karthik
August 12, 2008 at 6:09 am
A little modification in the code.
Just change the second update statement.
Update #t1
set Name2 = isnull(( select Name
from #t1 a
where a.ID = #t1.ID and a.Seq in ( select min(Seq) from #t1 where Seq not in (select min(Seq) from #t1 group by ID)
group by ID)),'')
karthik
August 12, 2008 at 6:13 am
In SQL2005, a CTE could be used.
A temp table can be avoided, in SQL2000, by doing something horrible like:
SELECT T1.[ID], T1.[NAME] AS Name1, T2.[NAME] AS Name2
FROM YourTable T1
    JOIN
    (
        SELECT T11.[ID], MIN(T11.SUB) AS SUB
        FROM YourTable T11
        WHERE T11.Status = 'Active'
        GROUP BY T11.[ID]
    ) D1
        ON T1.[ID] = D1.[ID]
            AND T1.SUB = D1.SUB
    LEFT JOIN
    (
        YourTable T2
        JOIN
        (
            SELECT T21.[ID], MIN(T21.SUB) AS SUB
            FROM YourTable T21
                JOIN YourTable T22
                    ON T21.[ID] = T22.[ID]
                        AND T21.SUB > T22.SUB
            WHERE T21.Status = 'Active'
                AND T22.Status = 'Active'
            GROUP BY T21.[ID]
        ) D2
            ON T2.[ID] = D2.[ID]
                AND T2.SUB = D2.SUB
    )
        ON T1.[ID] = T2.[ID]
August 12, 2008 at 6:15 am
Why i changed ?
Becuase if the table get one more row say for example
insert into Client_Table
select 9001,4,'Gail','Active'
Initial method won't do our need. So i have changed the code.
karthik
August 12, 2008 at 8:19 am
thanks to karthikeyan and ken.....i tested both the ways...both of them works great...
thanks again 🙂
August 18, 2008 at 4:54 am
Sudheer,
An Alternate way here.
select ID, firstone = MIN(SUB)
into #t
from Client_Table
where Status = 'Active'
group by ID
go
create unique clustered index idx1 on #t (ID, firstone)
go
select ID, secondone = MIN(SUB)
into #q
from Client_Table a
where Status = 'Active'
and SUB != (select firstone from #t b where b.ID = a.ID)
group by ID
go
select distinct a.ID, Name1 = c.Name, Name2 = d.Name
from #t a, #q b, Client_Table c, Client_Table d
where a.ID *= b.ID
and a.ID *= c.ID
and a.firstone *= c.SUB
and b.ID *= d.ID
and b.secondone *= d.SUB
karthik
August 18, 2008 at 4:56 am
One more Alternate way.
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
karthik
August 18, 2008 at 12:28 pm
Excellent Karthik
2nd Query works perfectly fine, but there is a problom in first query as this wont handle case where there is only 1 active client for an ID....example
ID name status
9001 paul 'Active'
9001 john 'Inactive'
this case o/p should be like this
9001 paul --(null)
but in 2nd query all such rows wont be considered as your #q2 will doesnt have any matching records .
plase correct me if i am wrong...thanks.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply