October 28, 2003 at 8:03 pm
Hi everyone,
I am looking for help for a TsqL LOOP IN A CURSOR.I have a table which looks like this.
Order OrderDescription OrderSequence
1 This item is the 1
1 greatest thing to 2
1 happen to the world 3
2 The fundamentals of 1
2 biology,specifically 2
2 microbiology and 3
2 parasitology speak 4
2 about bacteria 5
I tried writing a cursor(don't know if that's a good thing to do) and have a while @@fetcj]h = 0 loop, which concatenates all the order descriptions, based on a select ordered by OrderSequence and inseerts into a table Newtable. The problem which i run into is, coding a loop to move on to Order 2, after the inner loop concatenates the orderdescriptions. TSql is preety new to me and all the help, that i can get is appreciated.
Aspiring Programmer
October 29, 2003 at 6:30 am
If you are trying to learn about cursors, here is an example of what you describe:
SET NOCOUNT ON
DECLARE @OrderNo int, @Desc varchar(30)
DECLARE @LastOrdNo int, @NewDesc varchar(2000)
DECLARE Cur CURSOR FOR
SELECT OrderNo, OrderDescription
FROM Orders
ORDER BY OrderNo, OrderSequence
OPEN Cur
FETCH NEXT FROM Cur INTO @OrderNo, @Desc
SET @LastOrdNo = @OrderNo
WHILE @@FETCH_STATUS = 0 BEGIN
IF @LastOrdNo = @OrderNo
SET @NewDesc = ISNULL(@NewDesc + ' ','') + @Desc
ELSE BEGIN
INSERT NewTable
VALUES(@LastOrdNo, @NewDesc)
SET @LastOrdNo = @OrderNo
SET @NewDesc = @Desc
END
FETCH NEXT FROM Cur INTO @OrderNo, @Desc
END
IF @LastOrdNo IS NOT NULL
INSERT NewTable
VALUES(@LastOrdNo, @NewDesc)
CLOSE Cur
DEALLOCATE Cur
If you're using SQL Server 2000, you could also use a concatenation trick with a UDF:
CREATE FUNCTION dbo.f_BuildDesc(@OrderNo int)
RETURNS varchar(2000) AS BEGIN
DECLARE @Desc varchar(2000)
SELECT @Desc = ISNULL(@Desc + ' ','') + OrderDescription
FROM Orders
WHERE OrderNo = @OrderNo
ORDER BY OrderSequence
RETURN @Desc END
SET NOCOUNT ON
INSERT NewTable
SELECT DISTINCT OrderNo, dbo.f_BuildDesc(OrderNo)
FROM Orders
Here is that same trick, but used with a cursor rather than a UDF:
SET NOCOUNT ON
DECLARE @OrderNo int, @Desc varchar(2000)
DECLARE Cur CURSOR FOR
SELECT DISTINCT OrderNo
FROM Orders
OPEN Cur
FETCH NEXT FROM Cur INTO @OrderNo
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @Desc = ISNULL(@Desc + ' ','') + OrderDescription
FROM Orders
WHERE OrderNo = @OrderNo
ORDER BY OrderSequence
INSERT NewTable
VALUES(@OrderNo, @Desc)
SET @Desc = NULL
FETCH NEXT FROM Cur INTO @OrderNo
END
CLOSE Cur
DEALLOCATE Cur
--Jonathan
Edited by - Jonathan on 10/29/2003 09:33:46 AM
--Jonathan
October 29, 2003 at 7:50 am
Here is a way to do that by creating a pivot table query, no cursors here.
set nocount on
create table pivot_records ([Order] int,
OrderDescription varchar(100),
OrderSequence int)
insert into pivot_records values(1, 'This item is the', 1)
insert into pivot_records values(1, 'happen to the world', 3)
insert into pivot_records values(1, 'greatest thing to', 2)
insert into pivot_records values(4, 'One more example for',1)
insert into pivot_records values(4, 'good measure',2)
insert into pivot_records values(2, 'The fundamentals of', 1)
insert into pivot_records values(2, 'biology,specifically', 2)
insert into pivot_records values(2, 'microbiology and', 3)
insert into pivot_records values(2, 'parasitology speak', 4)
insert into pivot_records values(2, 'about bacteria', 5)
-- declare variables
declare @p char(1000)
declare @i char(5)
declare @l int
declare @m int
-- Print Report Heading
print 'Order' + ' OrderDescription'
print '----- ' + '------------------------------------------'
set @p = ''
select top 1 @l=[Order] from pivot_records
order by [Order] desc
-- Set @m less than the first order
set @m = 0
-- Process until no more items
while @m < @l
begin
-- increment Order number
select top 1 @m = [Order] from pivot_records
where [Order] > @m
order by [Order]
-- string together all Descriptions with a space between
select @i = [Order], @p = rtrim(@p) + ' ' + OrderDescription
from pivot_records a
where [Order] = @m
order by OrderSequence
-- print detail row
print @i + rtrim(@p)
set @p = ''
end
-- remove example table
drop table pivot_records
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 29, 2003 at 9:20 am
Thanks a lot to Jonathan and Greg Larsen.
These were the exact tips and suggestions i was looking for, and appreciate the prompt responses. I will try out both the solutions.
Can't thank enpugh again!!!
October 30, 2003 at 4:56 am
Hi,
What Jonathan given is quite intellegible solution to the topic. I really learn a lot from those queries.
Thanks Jonathan...,
Madhu
Madhu
November 1, 2007 at 9:22 am
The problem with cursors on big datasets is performance - batch processing is usually preferred. Same is probably true with any approach that processes records one by one. Since you already have OrderSequence column (and assuming there are not too many records per order and all orders always have a record where [OrderSequence]=1), you can try this:
declare @i int, @max-2 int
select @max-2 = max([OrderSequence]) from [Orders]
insert [NewTable] ([Order], [OrderDescription])
select [Order], ltrim(rtrim([OrderDescription]))
from [Orders]
where [OrderSequence] = 1
set @i = 2
while @i <= @max-2 begin
update [NewTable]
set
[OrderDescription] = n.[OrderDescription] + ' ' + ltrim(rtrim(o.[OrderDescription]))
from
[NewTable] n
join [Orders] o on n.[Order] = o.[Order]
where
o.[OrderSequence] = @i
and o.[OrderDescription] is not null
and o.[OrderDescription] != ''
set @i = @i + 1
end
I haven't tested this script, but I have implemented this solution in the past.
November 1, 2007 at 11:23 am
Unless you are an "expert" (god knows what this means...) you should be much better off staying away from cursors.
Although it's not exactly easy to read... I still much prefer this
/* run this once only
set nocount on
create table SomeTable (OrderId int,
OrderDescription varchar(100),
OrderSequence int)
insert into SomeTable values(1, 'This item is the', 1)
insert into SomeTable values(1, 'happen to the world', 3)
insert into SomeTable values(1, 'greatest thing to', 2)
insert into SomeTable values(4, 'One more example for',1)
insert into SomeTable values(4, 'good measure',2)
insert into SomeTable values(2, 'The fundamentals of', 1)
insert into SomeTable values(2, 'biology,specifically', 2)
insert into SomeTable values(2, 'microbiology and', 3)
insert into SomeTable values(2, 'parasitology speak', 4)
insert into SomeTable values(2, 'about bacteria', 5)
*/
WITH Orders(Id)
AS (
SELECT DISTINCT OrderId FROM SomeTable
)
SELECT Id,
(
SELECT OrderDescription + ' / '
FROM SomeTable
WHERE OrderId = Id
ORDER BY OrderId, OrderSequence
FOR XML PATH('')
) AS Descriptions
FROM Orders
Don't worry, I did not invent it! 🙂
Eric
November 1, 2007 at 5:15 pm
Nice 2k5 solution... but I'm pretty sure that it won't work in 2k... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2007 at 5:35 pm
November 1, 2007 at 8:19 pm
Ack... agreed... I missed that... not enough coffee... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2007 at 3:01 am
Guys,
Yes, it is 2k5 specific.
Yes it is a correlated sub query but why is this a problem?
Thanks
Eric
November 2, 2007 at 6:58 am
If OrderSequence has a known upper limit this should do the trick. And if it should contain a correlated subquery, I beg everyone's pardon.:)
select [Order],max(od1)+' '+max(od2)+' '+max(od3)+' '+max(od4)+' '+max(od5)
from
(
select [Order],
case when OrderSequence=1 then OrderDescription else '' end od1,
case when OrderSequence=2 then OrderDescription else '' end od2,
case when OrderSequence=3 then OrderDescription else '' end od3,
case when OrderSequence=4 then OrderDescription else '' end od4,
case when OrderSequence=5 then OrderDescription else '' end od5
from pivot_records
) t
group by [Order]
November 2, 2007 at 8:43 am
emamet (11/2/2007)
Guys,Yes, it is 2k5 specific.
Yes it is a correlated sub query but why is this a problem?
Typically, yes... correlated subqueries tend to be slower than a nice derived table join. Your correlated subquery has an equi-join which isn't too bad (in-equality or triangular join would be much, much worse).
Michael Meierruth's suggestion is much more effective if you're dealing with known limits on the number of lines that could be in the description. If, in fact, you have very good controls on the Order and Sequence numbers (ie. unique combinations of those), then you might be able to simplify Michael's code even further...
--===== Using previous example of test data
set nocount on
create table SomeTable (OrderId int,
OrderDescription varchar(100),
OrderSequence int)
insert into SomeTable values(1, 'This item is the', 1)
insert into SomeTable values(1, 'happen to the world', 3)
insert into SomeTable values(1, 'greatest thing to', 2)
insert into SomeTable values(4, 'One more example for',1)
insert into SomeTable values(4, 'good measure',2)
insert into SomeTable values(2, 'The fundamentals of', 1)
insert into SomeTable values(2, 'biology,specifically', 2)
insert into SomeTable values(2, 'microbiology and', 3)
insert into SomeTable values(2, 'parasitology speak', 4)
insert into SomeTable values(2, 'about bacteria', 5)
--===== Reconstruction of OrderDescription simplified
SELECT OrderID,
MAX(CASE WHEN OrderSequence=1 THEN OrderDescription+' ' ELSE '' END)
+ MAX(CASE WHEN OrderSequence=2 THEN OrderDescription+' ' ELSE '' END)
+ MAX(CASE WHEN OrderSequence=3 THEN OrderDescription+' ' ELSE '' END)
+ MAX(CASE WHEN OrderSequence=4 THEN OrderDescription+' ' ELSE '' END)
+ MAX(CASE WHEN OrderSequence=5 THEN OrderDescription ELSE '' END)
FROM SomeTable
GROUP BY OrderID
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2007 at 8:45 am
... If the max number of Sequences is not known... we can convert this to something a bit more dynamic...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2007 at 8:47 am
Jeff,
Thanks for the explanation, and the last query.
Eric
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply