insert values from one table into another table with different design in sql server

Question

Hi there,

I'm back with another (possibly) silly question. sorry.

I have a pretty complicated query which joins 4 tables and computes the sum of a column based on the other two columns in two tables. the result returned is like this:

Now, I want the results to be like the right hand side of the image. the number rows per month/year might change though its 4 for now.

I am creating a temporary table as:
Declare @TmpTable (id int identity, AnsSum float, AnsMonth int, AnsYear int)
to store the values from image --> table1. However, I cant figure out how to convert those rows into the format required by table 2.

So, Any hints on this please? Thanks so much..

ps: I tried to google and related questions here, no luck.
pss: I am not expecting the exact answer too, i am quite interested to learn new things so if you know where i can learn to do this, a push in the right direction, that would be great too!

Answer 1

So you have normalized data and you want to pivot the result set to create repeating groups.

You could use PIVOT but you'd need some other attribute in your base table to define the four columns.

I would recommend do not pivot this query in SQL. Just do the query against the database and get four rows per month/year. Then write code in your application to aggregate the results by month/year.

Answer 2

You could use cross apply to get all the values in a comma delimted format in a single column. instead of "4" different columns. The problem is this "4" cannot be defined everytime. it may increase or decrease and it is not advisable to have this as columns.

SELECT DISTINCT AnaMonth, anayear, [DerivedColumn] FROM @TmpTable A CROSS APPLY ( SELECT AnaSum + ',' FROM @TmpTable B WHERE A.AnaMonth = B.AnaMonth AND A.AnaYear = B.AnaYear FOR XML PATH('') ) AS C (DerivedColumn)

You will get [6.0000, 1.000, 8.0000, 5.0000] in one column for month 5 and year 2010 etc ... You could use this as a table to query for any particular month.

Hope this helps