July 14, 2015 at 3:53 pm
Hi All,
I want to get the final output in single line from he below example. Is it posible ? Can some help.
Desired output is
2,data2,5,data5 ----In a single line.
Declare @a table
(
id int,
data varchar(10)
)
insert into @a values(1,'data1')
insert into @a values(2,'data2')
insert into @a values(3,'data3')
insert into @a values(4,'data4')
Declare @b-2 table
(
id int,
data varchar(10)
)
insert into @b-2 values(1,'data1')
insert into @b-2 values(3,'data2')
insert into @b-2 values(5,'data3')
insert into @b-2 values(4,'data4')
Select * From @a
Select * From @b-2
select * from @a a
full outer join @b-2 b on a.id=b.id
where b.data is null or a.data is null
July 15, 2015 at 8:05 am
Any thoughts on this ?
July 15, 2015 at 8:18 am
dhanekulakalyan (7/15/2015)
Any thoughts on this ?
Try this:
Declare @a table (
id int,
data varchar(10)
);
insert into @a values(1,'data1')
insert into @a values(2,'data2')
insert into @a values(3,'data3')
insert into @a values(4,'data4');
Declare @b-2 table (
id int,
data varchar(10)
);
insert into @b-2 values(1,'data1')
insert into @b-2 values(3,'data2')
insert into @b-2 values(5,'data3')
insert into @b-2 values(4,'data4');
Select * From @a
Select * From @b-2;
DECLARE @RESULT AS varchar(200) = '';
WITH RESULTS AS (
select ISNULL(a.id, b.id) AS id, ISNULL(a.data, b.data) AS data
From @a a
full outer join @b-2 b
on a.id=b.id
where b.data is null
or a.data is null
)
SELECT @RESULT = @RESULT + (
SELECT ', ' + CAST(R.id AS varchar(9)) + ', ' + R.data
FROM RESULTS AS R
FOR XML PATH('')
)
SELECT STUFF(@RESULT, 1, 2, '') AS THE_RESULT;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 15, 2015 at 8:32 am
You actually would need to divide your query as you're trying to create a relation between rows that are not related.
WITH AValues AS(
select a.id,
a.data,
ROW_NUMBER() OVER(ORDER BY a.id) AS rn
from a a
left join b b on a.id=b.id
where b.data is null
),
BValues AS(
select b.id,
b.data,
ROW_NUMBER() OVER(ORDER BY b.id) AS rn
from a a
right join b b on a.id=b.id
where a.data is null
)
SELECT a.id AS a_id,
a.data AS a_data,
b.id AS b_id,
b.data AS b_data
FROM Avalues a
FULL JOIN BValues b ON a.rn = b.rn;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply