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)
