January 25, 2010 at 3:45 am
All,
I have one scenario.
create table emp
(
eno int,
ename varchar(5),
isemp bit,
iscont bit,
isadmin bit,
ishr bit
)
insert into emp
select 1,'aa',1,0,0,1
union all
select 1,'aa',0,0,1,1
union all
select 1,'aa',1,0,0,1
union all
select 1,'aa',0,0,1,1
union all
select * from emp
1aa1001
1aa0011
1aa1001
1aa0011
Expected output:
1 isemp,iscont,ishr
isadmin won't come here. Because it has only '0' in all the rows.
karthik
January 25, 2010 at 4:12 am
No, ISCONT has zeros in all the records.
N 56°04'39.16"
E 12°55'05.25"
January 25, 2010 at 4:17 am
Here is a solution.
DECLARE@Emp TABLE
(
eno int,
ename varchar(5),
isemp bit,
iscont bit,
isadmin bit,
ishr bit
)
insert@emp
select1, 'aa', 1, 0, 0, 1 union all
select1, 'aa', 0, 0, 1, 1 union all
select1, 'aa', 1, 0, 0, 1 union all
select1, 'aa', 0, 0, 1, 1
;with ctePeso (eno, ename, title)
AS (
selectu.eno,
u.ename,
u.thecol
from@emp AS e
unpivot(
theValue
for theCol IN (e.isemp, e.iscont, e.isadmin, e.ishr)
) AS u
group byu.eno,
u.ename,
u.theCol
havingsum(sign(u.thevalue)) > 0
)
selecte.eno, e.ename,
stuff(f.q, 1, 1, '') AS Titles
from(select distinct eno, ename from @emp) AS e
cross apply (
select ',' + p.title
from ctepeso as p
where p.eno = e.eno and p.ename = e.ename
order by p.title
for xml path('')
) AS f(q)
N 56°04'39.16"
E 12°55'05.25"
January 25, 2010 at 4:17 am
Peso, I don't understand your answer as well as I don't understand the question.
Can you clarify?
-- Gianluca Sartori
January 25, 2010 at 4:20 am
OP, the senior application engineer, told us that ISADMIN columns had zeros in all the records. It is not true. It is column ISCONT that has zeros in all columns.
N 56°04'39.16"
E 12°55'05.25"
January 25, 2010 at 4:21 am
i think expected result is wrong!(isadmin would appear in the result but not iscont)
will this do?
select eno, CASE WHEN SUM(CASE WHEN isEmp = 1 THEN 1 ELSE 0 END) > 0 THEN 'isemp,' ELSE '' END +
CASE WHEN SUM(CASE WHEN isCont = 1 THEN 1 ELSE 0 END) > 0 THEN 'isCont,' ELSE '' END +
CASE WHEN SUM(CASE WHEN isadmin = 1 THEN 1 ELSE 0 END) > 0 THEN 'isadmin,' ELSE '' END +
CASE WHEN sum(CASE WHEN ishr = 1 THEN 1 ELSE 0 END) > 0 THEN 'ishr' ELSE '' END
from #emp
Group by eno
---------------------------------------------------------------------------------
January 25, 2010 at 4:29 am
Maybe I'm having a bad monday morning, but I don't see what the question has to do with the subject of the thread.
-- Gianluca Sartori
January 25, 2010 at 4:33 am
I think OP wants several methods to concatenate result, which should work in all three RDMS's.
What we provided is a way for SQL Server to deal with this.
It's a monday thing 😀
N 56°04'39.16"
E 12°55'05.25"
January 25, 2010 at 4:33 am
Gianluca Sartori (1/25/2010)
Maybe I'm having a bad monday morning, but I don't see what the question has to do with the subject of the thread.
nothing actually 😛 (I guess so) may be he wanted to give test data with that example (competency context) and later changed his mind to 'employee' context
---------------------------------------------------------------------------------
January 26, 2010 at 4:10 pm
{edit} never mind... I misunderstood something here very badly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply