loop without cursor?
-
Hi
I remebered someone posted the similar thread before. I searched but I just could not find it.
My table looks like this, datatype are all varchar
col1 col2
abc my
abc result
bbc his
bbc result
bbc good
I want the output looks like this
col1 col2
abc my result
bbc his result good
Is there a way to accomplish this without using a cursor to loop through and concatnate my col2
-
There is:
if object_id('data') is not null
drop table data
create table data (col1 varchar(20), col2 varchar(20), col_order int)
insert into data (col1, col2, col_order) values ( 'abc', 'my' , 1)
insert into data (col1, col2, col_order) values ( 'abc', 'result' , 2)
insert into data (col1, col2, col_order) values ( 'bbc', 'his' , 3)
insert into data (col1, col2, col_order) values ( 'bbc', 'result' , 4)
insert into data (col1, col2, col_order) values ( 'bbc', 'good ' , 5)
go
if object_id('cat') is not null
drop function cat
go
create function dbo.cat ( @col1 varchar(20) )
returns varchar(8000)
as
begin
declare @result varchar(8000)
select @result = coalesce( @result + ' ' , '') + col2
from data
where col1 = @col1
order by col_order
return @result
end
select distinct col1, dbo.cat (col1)
from data
Note that you *need* an "order" column because there is no way to gurrantee the order of the results that you get otherwise
Cheers,
* Noel -
Thanks a lot. It worked just the way I wanted.
-
Very cool! Is it possible to create a line break between the columns? I have the following data:
Task_Code Task_Description 12.069 Check and inspectcondensate pan and drain lines 12.069 Verify alignementof pulleys 12.069 Lube all motors and bearings as req'd 12.069 Check condition of belts and pulleys, change 12.069 as req'd 12.069 Inspect filters, replace as needed. 12.069 Check operation of outside air dampers 12.069 CONDENSERS: 12.069 Lubricate motor and fan bearings as req'd. 12.069 Inspect belts andsheaves, replace as req'd 12.069 Brush condenser fins as req'd 12.069 Inspect unit cabinet for proper integrity 12.069 Check and clean coil as req'd 12.069 ELECTRICAL: 12.069 Check all connections and contactors for wear 12.069 and pitting 12.069 Check sequence ofoperation of controllers, adju 12.069 st and calibrate as needed. 12.069 EVAPORATOR: 12.069 Check unit for any excessive noise or vibration. 12.069 Check interlocks for proper operation 12.069 Check coil and clean as req'd 12.069 Check operation of compressors with amp probe 12.069 Check operation of all safety controls 12.069 FAN SECTION: 12.069 Verify alignment of pulleys 12.069 Lube all motors and bearings as req'd 12.069 Check condition of belts and pulleys 12.069 replace as req'd 12.069 Inspect filters, replace as req'd 12.069 HEATING SECTION: 12.069 Check burner sequence of operation 12.069 Check for gas leaks, repair as req'd 12.069 Inspect electrical connections and contactors 12.069 for wear and pitting 12.069 Clean burners 15.015 Check fan, coil and condensate system for clean- 15.015 liness. 15.015 Monitor cooling capacity and heat rejection to 15.015 standard. 15.015 Check for alarms. 15.015 Check condensate pumps and inspect drains. 15.015 Replace air filters. 15.015 Secure all panels. I need it to result in a single task description text field for each task code. So in the result, there would be a task description text field for task code 12.069 and a task description text field for task 15.015. The task code itself does not need to be in the text field. Is there a way to integrate char(13) into the function
?Task_Code Task_Description
12.069 Check and inspectcondensate pan and drain lines
Verify alignementof pulleys
Lube all motors and bearings as req'd
Check condition of belts and pulleys, change
as req'd
Inspect filters, replace as needed.
Check operation of outside air dampers
CONDENSERS:
Lubricate motor and fan bearings as req'd.
Inspect belts andsheaves, replace as req'd
Brush condenser fins as req'd
Inspect unit cabinet for proper integrity
Check and clean coil as req'd
ELECTRICAL:
Check all connections and contactors for wear
and pitting
Check sequence ofoperation of controllers, adju
st and calibrate as needed.
EVAPORATOR:
Check unit for any excessive noise or vibration.
Check interlocks for proper operation
Check coil and clean as req'd
Check operation of compressors with amp probe
Check operation of all safety controls
FAN SECTION:
Verify alignment of pulleys
Lube all motors and bearings as req'd
Check condition of belts and pulleys
replace as req'd
Inspect filters, replace as req'd
HEATING SECTION:
Check burner sequence of operation
Check for gas leaks, repair as req'd
Inspect electrical connections and contactors
for wear and pitting
Clean burners
15.015 Check fan, coil and condensate system for clean-
liness.
Monitor cooling capacity and heat rejection to
standard.
Check for alarms.
Check condensate pumps and inspect drains.
Replace air filters.
Secure all panels.
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec -
sure just replace the comma "," for " + CHAR(13) " and you will be fine.
Mind you there is a limit of 8000 characters for the operation.
good luck.
* Noel -
This is a different problem... can be more easily done with the GROUPING key word in a group by. But not possible either way unless we know what is the PK so the order of the Task_Description is correctly maintained...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks for your help Noel. Perhaps I did not understand your response to my question, I tried the following approaches but was unable to make it work:
-------------------------------------------------
if object_id('cat') is not null
drop function cat
go
create function dbo.cat ( @Task_Code varchar(20) )
returns varchar(8000)
as
begin
declare @result varchar(8000)
select @result = coalesce(@result + 'Char(13)') + Task_Description
from Task_Description
where Task_Code = @Task_Code
order by col_order
return @result
end
----------------------------------------------------------------------
if object_id('cat') is not null
drop function cat
go
create function dbo.cat ( @Task_Code varchar(20) )
returns varchar(8000)
as
begin
declare @result varchar(8000)
select @result = coalesce(@result + ' ' Char(13) '') + Task_Description
from Task_Description
where Task_Code = @Task_Code
order by col_order
return @result
end
-----------------------------------------------------------------------------
I did get it to work in the following manner, however, the first line of the record is blank.
if object_id('cat') is not null
drop function cat
go
create function dbo.cat ( @Task_Code varchar(20) )
returns varchar(8000)
as
begin
declare @result varchar(8000)
select @result = coalesce(@result + ' ' , '') + Char(13) + Task_Description
from Task_Description
where Task_Code = @Task_Code
order by col_order
return @result
end
Am I missing something?
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec -
CREATE
FUNCTION [dbo].[GetTaskDesc] ( @Task_Code VARCHAR(20) )
RETURNS
VARCHAR(8000)
BEGIN
DECLARE @TaskDescVal VARCHAR(8000)
SET @TaskDescVal = ''
SELECT @TaskDescVal =@TaskDescVal + CASE WHEN @TaskDescVal = '' THEN '' ELSE CHAR(13) END + Task_Description
FROM
Task_Description
WHERE
Task_Description.Task_Code= @Task_Code
RETURN @TaskDescVal
END
GO
SELECT
Task_Code,
dbo
.[GetTaskDesc](Task_Code) AS Task_Description
FROM
Task_Description
GO
Prasad Bhogadi
www.inforaise.com -
Like I said, unless there is something to force the order of the Task Description (another column, usually), there is no guarantee that the descriptions will come out in the correct order within any given Task Code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Just to clarify, the loop w/o cursor did work fine with the exception of the initial blank line.
Thank you all for your suggestions.
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec -
But it won't always unless you have something that identifies the order of the task descriptions....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply