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 (https://blog.sqlauthority.com)
2 Comments. Leave new
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
Another method is to use Quirky method used in this link