question

nvnkmr10 avatar image
nvnkmr10 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
calculations
10 |1200

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

ThomasRushton avatar image
ThomasRushton answered
[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
10 |1200

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

nvnkmr10 avatar image
nvnkmr10 answered
can i do it with OUTER JOIN?
3 comments
10 |1200

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

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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 ·
nvnkmr10 avatar image nvnkmr10 commented ·
how i can use OUTER jOIn for this?
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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