using cursor
-
Hi - I need help using cursor, at least I think this is what I need to achieve the end result.
I have a table with property information, there is a schemeid which may be duplicated and for each line there is a property type desc it's the combination of schemeid and property desc that makes each record unique. I want to concatenate the property information to get one line of data for each schemeid, e.g.
schemeid 1234 1 bed 2 person house
schemeid 1234 2 bed 4 person house
would then be inserted into a new table as
schemeid 1234 1 bed 2 person house / 2 bed 4 person house
I have used the following to create a scheme count within my source table :
/* calculate scheme count */
declare @schemeid char(7), @mix varchar(45), @scheme_count int, @firstscheme char(7)
declare scheme_cursor cursor for
select schemeid, mix_details from rag_schememix
order by schemeid, mix_details
open scheme_cursor
fetch next from scheme_cursor
into @schemeid, @mix
set @firstscheme = @schemeid
set @scheme_count = 1
while @@fetch_status = 0
begin
if @schemeid <> @firstscheme
begin
set @scheme_count=1
end
update rag_schememix
set scheme_count = @scheme_count
where schemeid = @schemeid and mix_details = @mix
set @scheme_count= @scheme_count+1
set @firstscheme = @schemeid
fetch next from scheme_cursor
into @schemeid, @mix
end
close scheme_cursor
deallocate scheme_cursor
;
But I am having a problem writing something that will loop round the correct number of times for the schemeid / scheme_count combination.
I am fairly new to using the cursor function so any help would be very much appreciated.
Thanks
Helen.
-
Why are you even using a cursor or a loop to do this?
Please post the tables' definition, some sample data and the required output from that sample data specifying weither you want the output to be permanent (physical column in a table) or temporary (recalculated on every select).
-
Cancel my previous request.
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'ConcatDemo' AND XType = 'FN')
DROP FUNCTION dbo.ConcatDemo
GO
--Function to concatenate
CREATE FUNCTION dbo.ConcatDemo (@TableId INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Return AS VARCHAR(8000)
SELECT @Return = COALESCE(@Return + ', ' + Name, Name) FROM dbo.SysColumns WHERE ID = @TableId ORDER BY Name
RETURN @Return
END
GO
--Working code
DECLARE @Output TABLE (TableName SysName, ColsList VARCHAR(8000))
INSERT INTO @Output (TableName, ColsList)
SELECT OBJECT_NAME(O.Id) AS TableName, dbo.ConcatDemo (O.Id) AS ColList FROM dbo.SysObjects O WHERE O.XType = 'U'
SELECT * FROM @Output
GO
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'ConcatDemo' AND XType = 'FN')
DROP FUNCTION dbo.ConcatDemo
GO
-
Hi,
Thanks for the help but unfortunately I'm still having problems. This is quite advanced for me and I'm self taught so not really at the level of understanding the code you have supplied. Here's some data from my source table (rag_schememix) :
schemeid mix_details scheme_count
104 1 x 12FLAT 1
114 1 x 12FLAT 1
304 6 x 12FLAT 1
304 1 x 24FLAT 2
304 4 x 24HOUSE 3
What I'm trying to achieve in my destination table (rag_schememix_details) is one line of data per scheme with just the schemeid and the mix details, so for scheme 304 above :
schemeid Mix_concat
304 6 x 12FLAT / 1 x 24FLAT / 4 x 24HOUSE
I have created the scheme_count because I thought it would help me identify how many times to loop around the insert.
Any help would be very much appreciated.
Thanks
Helen.
-
Hi Helen,
Try the following script. The function provides the concatenation you require (I think). Any Q's, just post back.
-- Create table and insert test data
CREATE TABLE rag_schememix (schemeid SMALLINT, mix_details VARCHAR(20), scheme_count SMALLINT)
INSERT INTO rag_schememix VALUES(104,'1 x 12FLAT',1)
INSERT INTO rag_schememix VALUES(114,'1 x 12FLAT',1)
INSERT INTO rag_schememix VALUES(304,'6 x 12FLAT',1)
INSERT INTO rag_schememix VALUES(304,'1 x 24FLAT',2)
INSERT INTO rag_schememix VALUES(304,'4 x 24HOUSE',3)
GO
-- Drop Function if already exists
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE name = 'fnConcatenateMixDetails' AND xtype IN ('FN','IF','TF'))
DROP FUNCTION fnConcatenateMixDetails
GO
-- Create Function for Concatenation
CREATE FUNCTION fnConcatenateMixDetails(
@schemeID SMALLINT)
RETURNS VARCHAR(4000)
AS
BEGIN
DECLARE @return VARCHAR(4000)
SET @return = '' -- Select would return NULL if this not set
SELECT @return = @return + mix_details + ' / ' -- Concatenate Values
FROM rag_schememix
WHERE schemeID = @schemeID
ORDER BY scheme_count
SELECT @return = LEFT(@return, LEN(@return) - 1) -- Remove Trailing /
RETURN @return
END
GO
-- Select Details using function
SELECT DISTINCT schemeID, dbo.fnConcatenateMixDetails(schemeID)
FROM rag_schememix
ORDER BY schemeID
-- Tidy up
DROP TABLE rag_schememix
DROP FUNCTION fnConcatenateMixDetails
-
Thank you so much - it works!! I've been trying to find out about concatenation because I thought there should be a function but the SQL help was no help at all, that's why I was trying to use cursor. This is so simple, I will definately be using it again!
Helen.
-
Nicely done, Adrian...
--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)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply