Flattening many records into one

[jp777]

I have a table as below

product char(10)
date datetime
points integer(9)
flag char(1)

my requirement is to show the last (by date) 10 points (with dates) on one line, for all products

example

product points date points date points date (for 10)
product2 points date points date points date (for 10)
product3 points date points date points date (for 10)

any help is appreciated.

JP.

 

[mflancour]

You could use a stored proc; first use a group by query to get a list of all products, cursor though this..Inside this cursor use another cursor cycle through a query ordered by product, Date with a counter until the counter reaches 10 each time concatenating onto a variable that holds the points..ie set @v_points = @v_points + ', ' + @vc_points

 

[Andel]

can you give a sample table and the desired result of your query?

 

[jp777]

ok,

table description as above

4 sample records

'3562134' '20-01-2007 15:23:07' 99 'N'
'3562134' '20-01-2007 15:24:10' 101 'N'
'3562134' '20-01-2007 15:28:33' 103 'N'
'3562166' '20-01-2007 15:28:33' 140 'N'

desired output for these records

3562134 103 15:28:33 101 15:24:10 99 15:23:07
3562166 140 15:28:33

the output line is to contain the latest (by the date field) 10 records for each product any older records being dropped.

the output is to be a result of a stored procedure.

I'm going to work on the suggestion of mflancour and see what happens

 

[jp777]

mflancour,

many thanks, followed your guide, and with a bit of googling got it sorted.

Am very pleased .... thanks for your help.

jp