Help in query - SQL Server Forums

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.

All Forums

Other Forums

Other Topics

Help in query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-07 : 13:59:54

Kathy writes "Can any one please help me in creating query

Actual Data in database
ID Name Award Note
91 Maxine Ace Award Give on date 23rd February.
91 Maxine Ace Award Give on date 27th January.
91 Maxine Meal Voucher Give on date 5th June.
91 Maxine Meal Voucher Give on date 8th August.
92 Collette Ace Award Give on date 2nd January.
92 Collette Ace Award Give on date 3rd March.
92 Collette Ace Award Give on date 5th July
92 Collette Ace Award Give on date 9 April
92 Collette Meal Voucher Give on date 8th November.
92 Collette Meal Voucher Give on date 7th June.
92 Collette Meal Voucher Give on date 4th July.

Required Data
ID Name Award No of Times Note
91 Maxine Ace Award 2 Give on date 23rd February.Give on date 27th January.
91 Maxine Meal Voucher 2 Give on date 5th June.Give on date 8th August.
92 Collette Ace Award 3 Give on date 2nd January.Give on date 3rd March.Give on date 5th July
92 Collette Meal Voucher 3 Give on date 8th November.Give on date 7th June.Give on date 4th July.

Thanks,"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-08 : 12:54:21

[code]-- prepare test data
declare @test table (id int, name varchar(100), note varchar(100))

insert @test
select 91, 'Maxine Ace Award', 'Give on date 23rd February.' union all
select 91, 'Maxine Ace Award', 'Give on date 27th January.' union all
select 91, 'Maxine Meal Voucher', 'Give on date 5th June.' union all
select 91, 'Maxine Meal Voucher', 'Give on date 8th August.' union all
select 92, 'Collette Ace Award', 'Give on date 2nd January.' union all
select 92, 'Collette Ace Award', 'Give on date 3rd March.' union all
select 92, 'Collette Ace Award', 'Give on date 5th July' union all
select 92, 'Collette Ace Award', 'Give on date 9 April' union all
select 92, 'Collette Meal Voucher', 'Give on date 8th November.' union all
select 92, 'Collette Meal Voucher', 'Give on date 7th June.' union all
select 92, 'Collette Meal Voucher', 'Give on date 4th July.'

-- do the work
declare @stage table (id int, name varchar(100), notes varchar(8000))

insert @stage
select distinct id,
name,
''
from @test

declare @id int,
@notes varchar(8000)

select @id = min(id),
@notes = ''
from @stage

while @id is not null
begin
select @notes = left(@notes + note, 8000)
from (
select top 100 percent note
from @test
order by id
) z

update @stage
set notes = notes + @notes

select @id = min(id),
@notes = ''
from @stage
where id > @id
end

-- show the output
select * from @stage[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources