Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
 Posted - 2007-03-15 : 16:02:49
|
| Hi All,I am pretty new to SQL. I have no clue about Cursors.I have a table called PriceWithStoreCount$ with following fields & types:PriceZoneName(nvarchar),SaleDate (smalldatetime),ProductID (nvarchar),PriceStratPrice (decimal with precision 18 & scale 2),PriceWithStoreCount (nvarchar)Here are two examples of type of records that this table may have:PriceZoneName,SaleDate,ProductID,PriceStratPrice,PriceWithStoreCount 1Pen_North,2007-02-04,1-01820000466-000,1.75,1.75 (1 )1Pen_North,2007-02-04,1-01820000466-000,1.75,1.79 (4 )Now as you can see in above example there can be multiple PriceWithStoreCount per PriceZoneName per SaleDate per ProductID.I want to have one record as long as the PriceZoneName,SaleDate, and ProductID are same, and then in place of PriceWithStoreCount field I want to display a new Field let say PP which will have the multiple PriceWithStoreCount concatenated together (with a comma) in it.For above example this is what I want to display. (I have seperated each field & record with brackets)[PriceZoneName],[SaleDate],[ProductID],[PriceStratPrice],[PP] [1Pen_North],[2007-02-04],[1-01820000466-000],[1.75],[1.75 (1 ),1.79 (4 )]Note that in this example [PP] field has the following record:[1.75 (1 ),1.79 (4 )]Again remember there can be more than one PriceWithStoreCount per PriceZoneName,SaleDate, and ProductID. How can I achieve this using a cursor or anyother way.I am at work, and I have to finish this today. I would be really really thankfull if some one can create & show this whole cursor to me.Thanks.... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-16 : 02:25:18
|
| This will give you an idea how to start...SELECT PriceZoneName, SaleDate, ProductID, PriceStratPrice, MIN(PriceWithStoreCount), MAX(PriceWithStoreCount)FROM [PriceWithStoreCount$]GROUP BY PriceZoneName, SaleDate, ProductID, PriceStratPricePeter LarssonHelsingborg, Sweden |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-16 : 02:53:12
|
| And now you use a concat function to tie all information together.Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|
|
Quick help with a cursor !!!