Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Want to add Text data Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 2:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:15 AM
Points: 96, Visits: 179
Hi All,

I have table called T1 where the data is populated like below

LineNumber errortext Fileid
1 a 10
1 b 10
2 a 11
2 b 11
2 c 11
3 c 12
3 d 12

Now I have a table called t2 with same structure. Now I want to insert the records into t2 table and the data should be coming from T1 table. where the id is same it will take one id and one linenumber and concatenate of errortext field. My output will be like below

LineNumber errortext Fileid
1 a,b 10
2 a,b,c 11
3 c,d 12

File id should be taken once and then distinct of linenumber field and then concatenate of errortext field.

Thanks in advance for your help!!
Post #1496269
Posted Thursday, September 19, 2013 3:18 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 3,146, Visits: 1,367
Quick and dirty solution which would not scale that well but works as below. You may want to investigate
CLR concatenate aggregate

declare @a table (Linenumber int, ErrorText varchar(30),FileID int)

insert into @a values
(1, 'a', 10),
(1, 'b', 10),
(2, 'a', 11),
(2, 'b', 11),
(2, 'c', 11),
(3, 'c', 12),
(3, 'd', 12)

select * from @a

declare @FileID int, @LineNumber int
declare FileIDCursor cursor for select distinct FileID, LineNumber from @a
declare @AggResult table (LineNumber int, ErrorText varchar(1000), FileID int)

open FileIDCursor
fetch next from FileIDCursor into @FileID, @LineNumber
while @@FETCH_STATUS = 0
begin
declare @Concat varchar(1000) = ''
select @Concat = @Concat + ErrorText + ','
from @a
where FileID = @FileID and linenumber = @LineNumber
insert into @AggResult
select @LineNumber, left(@Concat,len(@Concat)-1), @FileID
fetch next from FileIDCursor into @FileID, @LineNumber
end
close FileIDCursor
deallocate FileIDCursor
select * from @AggResult


Fitz

Post #1496276
Posted Thursday, September 19, 2013 3:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 5,367, Visits: 9,913
Have a look at this blog. It's got several different options for concatenating.

John
Post #1496283
Posted Thursday, September 19, 2013 5:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:15 AM
Points: 96, Visits: 179
Thanks guys for your input


It is working awesome!!

Post #1496331
Posted Thursday, September 19, 2013 7:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
There is no need to resort to looping for this. You can instead use STUFF.

Thanks Mark for the ddl and sample data.

declare @a table (Linenumber int, ErrorText varchar(30),FileID int)

insert into @a values
(1, 'a', 10),
(1, 'b', 10),
(2, 'a', 11),
(2, 'b', 11),
(2, 'c', 11),
(3, 'c', 12),
(3, 'd', 12)

select Linenumber,
STUFF((select ',' + ErrorText
from @a a2
where a1.Linenumber = a2.Linenumber
order by a2.ErrorText
for xml path('')), 1, 1, '') as ErrorTexts
, FileID
from @a a1
group by Linenumber, FileID



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1496394
Posted Thursday, September 19, 2013 10:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
niladri.primalink (9/19/2013)
Thanks guys for your input


It is working awesome!!



"It"? What is "It"? What did you end up using? Please post the code that you ended up using.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1496697
Posted Friday, September 20, 2013 12:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:15 AM
Points: 96, Visits: 179
Sorry For using the word 'it'.

Actually the code what SScrazy sent to me is working for me.

That's why I wrote it is working for me.

If I hurt you then it is in my unbeknown.

Post #1496720
Posted Friday, September 20, 2013 1:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 5,367, Visits: 9,913
I think what Jeff is getting at, and I agree, is that you shouldn't necessary use the first solution you're given. Mark himself admitted that it's "quick and dirty" and that it won't work very well once your table starts to grow; he even suggested another method. I linked to an article that shows you half a dozen or so ways of doing it. I recommend you evaluate them all to see which works best. Yes, it's more work for you now, but it'll pay in terms of future performance and and of how well prepared you'll be next time you have to implement something like this.

John
Post #1496726
Posted Saturday, September 21, 2013 10:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:28 PM
Points: 35,215, Visits: 31,666
niladri.primalink (9/20/2013)
Sorry For using the word 'it'.

Actually the code what SScrazy sent to me is working for me.

That's why I wrote it is working for me.

If I hurt you then it is in my unbeknown.



Thank you for the concern but you didn't hurt me. I'm more concerned about what you ended up using because While Loops and cursors can be quite slow. That's why I wanted to know what you ended up using.

My recommendation would be to have a look at the code that Sean wrote for this. With apologies to Mark, Sean's code will run much more quickly.



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1497148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse