February 3, 2007 at 12:14 am
Hello all,
I'm a newbie with T-SQL here. What I'm trying to do simply move a data from vertical set to horizontal set. For example:
Book John
Bicycle John
PDA Jim
Desk John
Becomes:
John Book, Bicycle, Desk
Jim PDA
Anyway I wrote a stored procedure with two cursors running, but the concatenation seems to be failing. Can someone please give me any advise what did I do wrong? Thanks,
---------------------------------------------------------------------------------------------------------
if exists (select * from sysobjects where id = object_id('dbo.create_interestedstock') and sysstat & 0xf = 4)
drop procedure dbo.create_interestedstock
GO
CREATE PROCEDURE create_interestedstock
AS
BEGIN
DECLARE @contact varchar(18),
@client_company varchar(5),
@interestedstock varchar(50),
@clientcompany varchar(5),
@tmp_count int
DECLARE get_contact CURSOR FOR
SELECT contact from clientcompany
SELECT @contact = NULL
SELECT @client_company = NULL
SELECT @interestedstock = NULL
SELECT @clientcompany = NULL
SELECT @tmp_count = NULL
OPEN get_contact
FETCH NEXT FROM get_contact INTO
@contact
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @tmp_count = count(*)
FROM clientcompany
WHERE contact= @contact
IF @tmp_count = 1
BEGIN
SELECT @interestedstock = client_company
FROM clientcompany
WHERE contact =@contact
INSERT INTO newextract (contact, interestedstock)
VALUES (@contact,@interestedstock)
END
ELSE
BEGIN
DECLARE get_stock CURSOR FOR
SELECT client_company from clientcompany WHERE contact = @contact
OPEN get_stock
FETCH NEXT FROM get_stock INTO
@clientcompany
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @interestedstock = @clientcompany + ', '
FETCH NEXT FROM get_stock INTO @clientcompany
END
INSERT INTO newextract (contact, interestedstock)
VALUES (@contact,@interestedstock)
DEALLOCATE get_stock
END
SELECT @contact = NULL
SELECT @client_company = NULL
SELECT @interestedstock = NULL
SELECT @clientcompany = NULL
SELECT @tmp_count = NULL
FETCH NEXT FROM get_contact INTO
@contact
END
DEALLOCATE get_contact
END
GO
February 4, 2007 at 1:03 pm
Frequent Question - see "How do I concatenate strings from a column into a single row?"
SQL = Scarcely Qualifies as a Language
February 4, 2007 at 10:13 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply