# question

id year amount 1 1998 232 2 1998 111 2 1999 222 the new table should like id 1998_amount 1999_amount 1 232 0 2 111 222

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
[PIVOT][1] is the way to go. Have you ever done those before? Something like this: declare @SourceData table (id integer, YY integer, Amount integer) insert into @SourceData values (1, 1998, 232), (2, 1998, 111), (2, 1999, 222) select * from @SourceData select id, [1998] as [1998_amount], [1999] as [1999_amount] FROM ( SELECT id, YY, Amount FROM @SourceData) AS src PIVOT (SUM(Amount) FOR YY IN ([1998], [1999])) AS pvt [1]: http://msdn.microsoft.com/en-gb/library/ms177410(v=sql.105).aspx

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can i do it with OUTER JOIN?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yes, but what if you're asked to also look at Y2000, 2001, 2002 etc in the same query? You'll end up with four joins... The pivot can be expanded with very little effort, and will remain much more readable and maintainable.
0 Likes 0 ·
how i can use OUTER jOIn for this?
0 Likes 0 ·
Well, as you ask so nicely, how can I refuse? declare @SourceData table (id integer, YY integer, Amount integer) insert into @SourceData values (1, 1998, 232), (2, 1998, 111), (2, 1999, 222) select * from @SourceData ; WITH t1998 AS (SELECT id, SUM(Amount) AS a1998 FROM @SourceData WHERE YY = 1998 GROUP BY id), t1999 AS (SELECT id, SUM(Amount) AS a1999 FROM @SourceData WHERE YY = 1999 GROUP BY id) SELECT ISNULL( t1998.id, t1999.id), a1998 AS [1998_Amount], a1999 AS [1999_Amount] FROM t1998 FULL OUTER JOIN t1999 ON t1998.id = t1999.id
0 Likes 0 ·