multiple rows into one row: manipulating the data

[Brian]

I'm not sure if this is best done in SQL or .NET but I'm betting .NET
(hence the post here).

I've got data looking like this...


ProductID PriceGroup Price
1 List 1.00
1 Jobber 0.90
1 Retail 3.00

The client wants an interface like:

ProductID ListPrice JobberPrice RetailPrice
1 1.00 .90 3.00

I've gone back and forth in my planning to the point of total
confusion. If you've done anything like this before could you give me
suggestions?

eg. If I use a datagrid then should I pull a dataset truly
representing the datasource, fix the data into a datagrid as the
client desires(??how I'm not sure), then strip out the changes and do
some updates to the dataset tables and use command builder to update
the database from the dataset. (I'm not crazy about a solution like
this since in reality there are description fields and other info from
the database that is joined in .. like userID, roleID, etc that
determines who has access to editing the products ... and this would
mean a lot of tables pulled down.

-
Brian

 

[Michael]

Do it in SQL, it doesn't appear to be that complicated, I can't tell you an
exact query off the top of my head, but i'm sure someone in
microsoft.public.sqlserver.programming would help with that part.

--Michael

 

[Brian]

Do it in SQL, it doesn't appear to be that complicated, I can't tell you an
exact query off the top of my head, but i'm sure someone in
microsoft.public.sqlserver.programming would help with that part.

--Michael

Thanks Michael.
I'll go ask there.

I can figure out a way to do it (though looping gives me some
problems) but then I figured it would be hard to do the updates
afterwards -- so I was looking to see who had experience with it.

I might just back down from the challenge and try and convince my
client how nice it would look in a simpler (for me) format.

-
Brian