February 14, 2013 at 9:58 am
I have a two tables as below
Trackthis and trackingthis
Trackthis has
TrkthisID name Active with data as (1, Call, True)
(2, Could, True)
Trackingthis has
ProblemId TrkThisID IsActive with data as
( 51, 1,True)
( 51, 2,True)
And I have to concatenate the two rows but while concatenating I have to get the name value so the result should be something like below
51, Call and Could selected
51 Call only
51 could only
How can I accomplish this any suggestions
i have tried
SELECT DISTINCT STUFF
( (SELECT '*' + TrkthisId from Trackingthis FOR XML PATH('')),1,1,'')
as Combined FROM Trackingthis
Msg 245, Level 16, State 1, Line 12
Conversion failed when converting the varchar value '*' to data type smallint.
but get an error as
February 14, 2013 at 10:37 am
Here's a possible solution, if I've understood your requirements:
--== Create test data ==--
use tempdb;
go
drop table dbo.Trackthis;
drop table dbo.Trackingthis;
create table dbo.Trackthis
(
TrkthisID int,
name varchar(50),
Active char(5)
);
insert Trackthis values (1, 'Call', 'True');
insert Trackthis values (2, 'Could', 'True');
create table dbo.Trackingthis
(
ProblemId int,
TrkThisID int,
IsActive char(5)
);
insert Trackingthis values (51, 1,'True');
insert Trackingthis values (51, 2,'True');
insert Trackingthis values (52, 1,'True');
insert Trackingthis values (53, 2,'True');
--== Suggested Solution ==--
with cte1 as
(
select PROB.ProblemId, REF.Name
from Trackingthis PROB
inner join Trackthis REF on REF.TrkThisID = PROB.TrkThisID
where PROB.TrkThisID = 1
),
cte2 as
(
select PROB.ProblemId, REF.Name
from Trackingthis PROB
inner join Trackthis REF on REF.TrkThisID = PROB.TrkThisID
where PROB.TrkThisID = 2
)
select ISNULL(CTE1.ProblemId, CTE2.ProblemId) as ProblemId,
case
when CTE1.ProblemId IS NULL
then CTE2.Name + ' only'
when CTE2.ProblemId IS NULL
then CTE1.Name + ' only'
else
CTE1.Name + ' and ' + CTE2.Name + ' selected'
end as [Description]
from CTE1
full outer join CTE2 on CTE1.ProblemId = CTE2.ProblemId
No doubt there are much more efficient possibilities...
February 14, 2013 at 2:24 pm
thanks LAurie
however when i use the query in production environment i get the same result 4 times is there any other way i can accomplish this
February 15, 2013 at 6:11 am
Can you post some sample data in the same format as my test data. That would show what the problem is.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply