May 10, 2011 at 3:43 am
Hi,
I have a table which has the following records:
id;name
1;Pedro
2;Paula
I have a second table which has the fllowing records:
id;Soc
1;0041
1;002544
1;989898
2;8887
What i want isto make a query that returns this:
pedro;0041
NULL;002544
NULL;989898
Paula;8887
Obs - the join key between this two tables is the ID column.
Thank you
May 10, 2011 at 3:59 am
Try below SQL,
DECLARE @t1 TABLE(
id INT,
name VARCHAR(10))
DECLARE @t2 TABLE(
id INT,
soc VARCHAR(10))
INSERT INTO @t1
(id,
name)
SELECT 1,
'Pedro'
UNION ALL
SELECT 2,
'Paula';
INSERT INTO @t2
(id,
soc)
SELECT 1,
'0041'
UNION ALL
SELECT 1,
'002544'
UNION ALL
SELECT 1,
'989898'
UNION ALL
SELECT 2,
'8887'
SELECT CASE
WHEN row = 1 THEN name
ELSE NULL
END AS name,
soc
FROM (SELECT Row_number() OVER(PARTITION BY t1.id ORDER BY t1.id) row,
name,
soc
FROM @t1 t1
LEFT OUTER JOIN @t2 t2
ON t1.id = t2.id) t
May 12, 2011 at 2:48 am
thank you very much.
Problem solved.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply