SQL SERVER – Readers Contribution to Site – Simple Example of Cursor

eaders are very important to me. Without their active participation this site would not be the community helping web site. I encourage readers participation and request that you help other users with your knowledge.

I recently come across very good communication between two of blog readers. I want to thank you Imran Mohammed for taking time to answer this question as well many other questions. Expert like Imran makes this world better.

Let us read the question from Anthony from here.

All,

I am using Microsoft SQL 2005 and am trying to create a cursor that will take data from several records and concatenate that data into one record.

For example:

I have three attributes (OrderYear, SeqNumber, Item) from a single table (table name: TSGItems) that I am working with…

The data in TSGItems table looks like this:

OrderYear SeqNumber Item

2008 10001 Senior IRS Lien

2008 10001 IRS Lien

2008 10002 Senior IRS Lien

2008 10003 IRS Lien

2008 10003 Senior IRS Lien

2008 10003 Jr. Lien

2008 10004 HOA Lien

In addition to the concatenation, I am trying to manipulate the data as follows:

1) I would like to order the items alphabetically.

2) I would like to put an “and” before the last concatenation when there are more than 1 Item for each Orderyear and SeqNumber.

3) I would like to put in a “,” between each concatenation when there are 3 or more Item for each Orderyear and SeqNumber (except in between the last two concatenation.

4) I would like this data inserted into a temp table called #TSGItems

When I do a select statement on #TSGItems, I would like the data shown as follow:

OrderYear SeqNumber Item

2008 10001 IRS Lien and Senior IRS Lien

2008 10002 Senior IRS Lien

2008 10003 Jr. Lien, IRS Lien, and Senior IRS Lien

2008 10004 HOA Lien

I thank you for taking the time to look at and attempt to solve my challenge.

-Anthony

Let us read the answer from Imran from here.

@Anthony

Let me tell you I am not a developer and I dont have much developing skills… I am sure there must be another good way of writing sql code for what you have asked…

Below is my code, This does exactly what you described in your post.

CREATE TABLE TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
INSERT INTO TGSITEMS (ORDERYEAR, SEQNUMBER, ITEM)
SELECT '2008' ,10001 , 'Senior IRS Lien' UNION ALL
SELECT '2008' , 10001 , 'IRS Lien' UNION ALL
SELECT '2008' , 10002 , 'Senior IRS Lien' UNION ALL
SELECT '2008' ,10003, 'IRS Lien' UNION ALL
SELECT '2008' ,10003 , 'Senior IRS Lien' UNION ALL
SELECT '2008' ,10003, 'Jr. Lien' UNION ALL
SELECT '2008' ,10004, 'HOA Lien'

We created a table TGSITEMS and we inserted all the values which you mentioned in your post.

Then I created two more temporary tables #TGSITEMS and #TGSITEMS1… and with the help of While loop and cursor I got your result.

CREATE TABLE #TGSITEMS ( ORDERYEAR DATETIME, SEQNUMBER INT , ITEM VARCHAR(MAX))
GO
CREATE TABLE #TGSITEMS1 ( ID INT IDENTITY, ITEM VARCHAR(MAX))
GO
DECLARE @VAR INT
DECLARE
@CUR CURSOR
DECLARE
@VAR1 INT
DECLARE
@CMD VARCHAR(1000)
DECLARE @CMD1 VARCHAR(1000)
SET @CUR = CURSOR FOR
SELECT DISTINCT
SEQNUMBER
FROM TGSITEMS
OPEN @CUR
FETCH NEXT
FROM @CUR INTO @VAR
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE
#TGSITEMS1
INSERT INTO #TGSITEMS1 (ITEM) SELECT ITEM FROM TGSITEMS WHERE SEQNUMBER = @VAR ORDER BY ITEM
SET @VAR1 = 1
SET @CMD1 = ''
WHILE @VAR1 <= ( SELECT COUNT(*) FROM #TGSITEMS1 )
BEGIN
SELECT
@CMD = ITEM FROM #TGSITEMS1 WHERE ID = @VAR1
IF @VAR1 <(SELECT COUNT(*) FROM #TGSITEMS1) -1
SET @CMD1 = @CMD1 + @CMD +' , '
ELSE IF @VAR1 = (SELECT COUNT(*) FROM #TGSITEMS1 )-1
SET @CMD1 = @CMD1 + @CMD
ELSE IF (SELECT COUNT(*) FROM #TGSITEMS1 ) = 1
SET @CMD1 = @CMD
ELSE
SET
@CMD1 = @CMD1 + ' and ' + @CMD
SET @VAR1 = @VAR1 + 1
END
INSERT INTO
#TGSITEMS SELECT DISTINCT ORDERYEAR , SEQNUMBER, @CMD1 FROM TGSITEMS WHERE SEQNUMBER = @VAR
FETCH NEXT
FROM @CUR INTO @VAR
END
CLOSE
@CUR
DEALLOCATE @CUR
GO
SELECT DATENAME ( YY, ORDERYEAR)ORDERDATE , SEQNUMBER, ITEM FROM #TGSITEMS
GO
TRUNCATE TABLE #TGSITEMS
GO

Let me tell you this is highly performance consuming process… its upto you if you want to use it…

Hope this helps.
Thanks
Imran.

It was very good comment from Imran and here is Anthony’s response to Imran’s answer.

Thank you Imran,
I really appreciate you taking the time to address this challenge. I have been trying to move away from MS Access and am new to the SQL environment. I agree, what I am asking for is very demanding, but I feel reverting to Access is a step backward. I cannot wait to try the coded at work when I return on Monday.

Cheers
-Anthony

I thank you both of you to using this platform for helping solve technical problems.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

2 thoughts on “SQL SERVER – Readers Contribution to Site – Simple Example of Cursor

  1. This is my solution (please fix the previous one):

    declare @totCount int, @count int,
    @Year datetime, @seq int, @item varchar(100)

    declare @TGSITEMS table (IDEN INT IDENTITY(1, 1), ORDERYEAR DATETIME, SEQNUMBER INT, ITEM VARCHAR(100))
    declare @TSGItems table (ORDERYEAR DATETIME, SEQNUMBER INT, ITEM VARCHAR(2000), PRIMARY KEY (ORDERYEAR, SEQNUMBER))

    INSERT INTO @TGSITEMS (ORDERYEAR, SEQNUMBER, ITEM)
    SELECT ‘2008’ ,10001 , ‘Senior IRS Lien’ UNION ALL
    SELECT ‘2008’ , 10001 , ‘IRS Lien’ UNION ALL
    SELECT ‘2008’ , 10002 , ‘Senior IRS Lien’ UNION ALL
    SELECT ‘2008’ ,10003, ‘IRS Lien’ UNION ALL
    SELECT ‘2008’ ,10003 , ‘Senior IRS Lien’ UNION ALL
    SELECT ‘2008’ ,10003, ‘Jr. Lien’ UNION ALL
    SELECT ‘2008’ ,10004, ‘HOA Lien’
    ORDER BY 3 desc

    set @totCount = @@ROWCOUNT

    insert into @TSGItems
    select distinct ORDERYEAR, SEQNUMBER, ”
    from @TGSITEMS

    set @count = 1
    while @count 0 then ‘, ‘
    else ‘ and ‘
    end + ITEM
    where ORDERYEAR = @Year and SEQNUMBER = @seq

    set @count = @count + 1
    end

    select * from @TSGItems

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s