combining many rows into one

[joshz]

Please help me figure out a solution to this problem.

I have a table that tracks the amount of money received from various companies. In some cases there is more than one amount received from the same company. Here is some sample data from the table:

CompanyID, Date, Amount, Code

5, 12/18/2000, $250.00, B M
5, 7/3/2001, $350.00, SP
8, , $100.00, M
9, , $150.00, M
9, , $250.00, gen

I need to create a new table so that all data associated with the same CompanyID appears in the same row. See example below:

CompanyID, Date, Amount, Code, Date2, Amount2, Code2

5, 12/18/2000, $250.00, B M, 7/3/2001, $350.00, SP
9, , $150.00, M, , $250.00, gen

I know this is inefficient in terms of database design. However the reason I need this new table is to export the data into excel for further manipulation.

Any ideas on how I can accomplish this task? Any help would be appreciated. Thanks. -Josh

 

[jhall156]

Create the new table first, then

INSERT TheNewTable
SELECT company, SUM(amount), TheRestOfTheFields
FROM YourTable
GROUP BY company

JHall

 

[joshz]

I do not want to perform any calculations like SUM. I just want to have all the data associated with the same CompanyID combined into one row. See my original posting for an example.

Any ideas how I can do this?

 

[jhall156]

Oops sorry,
so you want to pivot the dates into columns? JHall

 

[Guest_imported]

hi ,

this problem can partly be solved by using JOIN - however if u have more than one row for same companies - it would be difficult. and still in JOIN some extra rows come
but which can be avoided by giving conditions.

e.g.
select * from table a , table b
where a.comp_no = b.comp_no and
a.amt != b.amt;

best of luck.
pravin.