asked
to add columns
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
[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
can i do it with OUTER JOIN?
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.
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
12 People are following this question.
