Combining two rows in one record

Question

question

SQLWorker asked Oct 12, '09

Combining two rows in one record

Hi,I am having two pivot tables which I am combining using union. The first table gives me records for first 3 columns and second query gives me records for remaining columns by checking maximum ID of the column filled by first query. But these two records are shown as two different rows in result set. I want to have a single record/row in output set which will combine these two rows with similar data in it. Can someone help me with this?

For e.g.: First query returns results such as:

Column1 Column2 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec                    
a       c1       1  1   1   0   0   0   0   0   0   0   0   0 - o/p from 1st query                    
a       c1       0  0   0   1   1   1   1   1   1   1   0   0 - o/p from 2nd query                    
a       c2       1  1   1   0   0   0   0   0   0   0   0   0 - o/p from 1st query

I want to have a result set as below:

Column1 Column2 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec                    
a       c1       1  1   1   1   1   1   1   1   1   1   0   0                    
a       c2       1  1   1   0   0   0   0   0   0   0   0   0

sql-server-2008 pivot union

10 |1200

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

Answer 1 · 2009-10-12T22:34:14Z

Squirrel 1 answered Oct 12, '09

select column1, column2, Jan = sum(Jan), Feb = sum(Feb), . . .            
from   (            
           select Column1, Column2, Jan, Feb, Mar . .. from table1            
           union all            
           select Column1, Column2, Jan, Feb, Mar . .. from table2            
       ) q            
group by column1, column2

10 |1200

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

question