December 23, 2010 at 9:13 am
Hi
2010 and 2009 records are there in same table how to join them
declare @Temp table (Value varchar(10),Tyear int)
insert into @Temp
Select 'A',2009
UNION ALL Select 'B',2009
UNION ALL Select 'M',2009
insert into @Temp
Select 'A',2010
UNION ALL Select 'B',2010
SELECT c.Value,p.Value ,
p.Tyear,c.Tyear from
@Temp as p Right join @Temp as c
on p.Value=c.Value
and p.Tyear=2010
and c.Tyear=2009
order by c.Value
I need o/p as
AA20102009
BB20102009
MNULLNULL2009
but
ANULLNULL2010
BNULLNULL2010
this 2 lines are coming extra
how to code while joining same table
Or the o/p which is coming is correct if so how
Thanks
Parthi
Thanks
Parthi
December 23, 2010 at 11:48 am
Are you interested in 2010 rows only if they match another 2009 row on the Value column? Or are you also interested in 2010 rows that have no matching 2009 row?
DECLARE @Temp TABLE (
Value varchar(10) NOT NULL,
Tyear int NOT NULL
)
INSERT INTO @Temp (Value, Tyear)
SELECT 'X', 2008 UNION ALL
SELECT 'A', 2009 UNION ALL
SELECT 'B', 2009 UNION ALL
SELECT 'M', 2009 UNION ALL
SELECT 'A', 2010 UNION ALL
SELECT 'B', 2010 UNION ALL
SELECT 'N', 2010 UNION ALL
SELECT 'X', 2011
Here's the query if you don't want 2010 rows with no matching 2009 row.
SELECT Y0.Value, Y0.Tyear, Y1.Value, Y1.Tyear
FROM @Temp AS Y0 LEFT OUTER JOIN @Temp AS Y1
ON (Y0.Value = Y1.Value AND Y0.Tyear = Y1.Tyear - 1)
WHERE (Y0.Tyear = 2009)
ORDER BY Y0.Value
Here's the query if you want rows from both 2009 and 2010 with no matching row from the other year.
SELECT Y0.Value, Y0.Tyear, Y1.Value, Y1.Tyear
FROM @Temp AS Y0 FULL OUTER JOIN @Temp AS Y1
ON (Y0.Value = Y1.Value AND Y0.Tyear = Y1.Tyear - 1)
WHERE (Y0.Tyear = 2009 OR Y1.Tyear = 2010)
ORDER BY COALESCE(Y0.Value, Y1.Value)
Here are alternative methods that use CTEs, which might be slightly more intuitive to you. I'm not sure which would be better in terms of performance.
;WITH
Y0 AS (SELECT Value, Tyear FROM @Temp WHERE Tyear = 2009),
Y1 AS (SELECT Value, Tyear FROM @Temp WHERE Tyear = 2010)
SELECT Y0.Value, Y0.Tyear, Y1.Value, Y1.Tyear
FROM Y0 LEFT OUTER JOIN Y1 ON (Y0.Value = Y1.Value)
ORDER BY Y0.Value
;WITH
Y0 AS (SELECT Value, Tyear FROM @Temp WHERE Tyear = 2009),
Y1 AS (SELECT Value, Tyear FROM @Temp WHERE Tyear = 2010)
SELECT Y0.Value, Y0.Tyear, Y1.Value, Y1.Tyear
FROM Y0 FULL OUTER JOIN Y1 ON (Y0.Value = Y1.Value)
ORDER BY COALESCE(Y0.Value, Y1.Value)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply