InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 19:04:55 GMT20http://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxThis is some useful code, but I have a bit of a challenge with it. Or should I say "I need it changed a bit".What if I need another 'level'. like this;Create Table TestTable (RowID Int Identity(1, 1), Lognumber int, Seq int, HDText varchar(100))Insert TestTable Values (95224,1,'THIS IS A SUGGESTION. WOULD IT BE POSSIBLE FOR US TO ')Insert TestTable Values (95224,1,'SYSTEMISE THE WACKY IDEA SYSTEM? IT CAN BASICALLY TAKE ANY FORM ON THE')Insert TestTable Values (95224,1,'SYSTEM. THE OPERATOR CAN COMPLETE THIS ')Insert TestTable Values (95224,1,'WITH WHAT IT WAS THAT THEY WANTED TO SEND THE DBA, AND THIS DE- ')Insert TestTable Values (95224,1,'FAULTS TO THE WACKEY OPERATOR IN EACH OFFICE. THE CORRECT ADDRESS TO ')Insert TestTable Values (95224,1,'WHERE THE HEADSLAP MUST BE SENT, WILL THEN BE ON THE DB ALONG WITH ALL')Insert TestTable Values (95224,1,'OTHER DETAIL. THE WACKEY OPERATOR CAN THEN COMPLETE THIS, AND THERE ')Insert TestTable Values (95224,1,'WILL BE RECORD ON THE SYSTEM OF ALL WACKEYS, WHEN , WHERE AND HOW ')Insert TestTable Values (95224,1,'THE HEADSLAP HAS BEEN GIVEN TO THE OPERATOR TO AVOID DUPLICATION, AND HEADSLAPS NOT GETTING DELIVERED ')Insert TestTable Values (95224,4,'Resource changed from TEAM2 ADMIN/SALES to PROJECTS STEERING COMMITTEE')Insert TestTable Values (95224,4,'Team changed from Team 2 - Admin/Sales/Data Xfer/M to Refer to steering committee)Insert TestTable Values (95224,13,'Resource changed from PROJECTS STEERING COMMITTEE to THE BIG BOSS')Insert TestTable Values (95224,13,'Team changed from Refer to steering committee to Team 14 - Management ')Insert TestTable Values (95224,18,'Team changed from Team 14 - Management to Computer Operations ')Insert TestTable Values (95224,18,SUGGESTION APPROVED BY Team 14 - Management ') select * from TestTableIf I need to Recurse by 'Lognumber' then by 'Seq' and then concatenate the text into 1 varchar field, how would I go about this?Would it also be possible to add a 'Char(10)' at the end of each 'seq' group?thanks in advance,Mon, 02 Aug 2010 05:14:05 GMTjumpinhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspx[quote][b]My question is this: What matters more,in large sets of data, execution time, or execution cost?[/b][/quote]As a DB guy, I think both of them are important. As a client, its the response time. So, both of them are important.But my point on the script by Nitin is just WHY would I ever like to alter my base table to get the concatenated values. Its not practical. Or would I create a temporary table for the data to get the things done. There are alot of issues involved. Considering 1 million rows example, its not workable. Plz clear me if I am missing something.Thu, 08 Jul 2010 22:48:39 GMTAtif-ullah Sheikhhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxAlright, so with all the different ways of doing this, I decided to run some tests and see which way would be most beneficial.I set out to try 1 million ([i]raise pinky finger to lips[/i]) rows of data. However, my data generator failed after 127k ish rows. So I used them to test all three of the presented ways of doing this. I had 10,000 groups in 127,971 rows of data. When I ran the statements a couple of things jumped out at me. First, it seemed that the execution plans for Nitin's Alter / Update / Select were coming in drastically under the other plans. I made a small change to Nigel's plan, and ran it against Nitin's.Nitin's plan came in at 20% of the batch (12% for the Alter / Update, 8% for Select), while Nigel's plan came in with the remaining 80%. I also noticed that Nitin's plan, for 127k records, was returning with a time right around 4.5 seconds, while the modified Nigel's plan was coming in just over half a second. (Atif's plan was coming in close to Nitin's, so I stopped looking there.)While I'm no expert on performance, I usually check things like this out if I'm not sure which solution to choose. [b]My question is this: What matters more,in large sets of data, execution time, or execution cost?[/b]Thu, 08 Jul 2010 10:35:28 GMTWesley Nortonhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxWhy to go hard way?No need to Add column ColRowNo and update it.I would go like this;[code="sql"]----------------------------------------------------------------------- Create Table and Insert data---------------------------------------------------------------------Create Table TestTable (RowID Int Identity(1, 1), Col1 int, Col2 char(3))Insert TestTable Values (1, 'ABC') Insert TestTable Values (1, 'DEF') Insert TestTable Values (1, 'GHI') Insert TestTable Values (2, 'JKL') Insert TestTable Values (2, 'MNO') Insert TestTable Values (3, 'PQR') Insert TestTable Values (3, 'STU') select * from TestTable----------------------------------------------------------------------- Add TestTable column and Insert Row Number---------------------------------------------------------------------Select Distinct Col1, (Stuff((Select ',' + Col2 from TestTable where Col1 = a.Col1 for XML Path('')),1,1,'')) from TestTable aDrop Table TestTable GO[/code]Wed, 07 Jul 2010 23:32:10 GMTAtif-ullah Sheikhhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxThanks Wesley,That certainly makes it easier to parse:-DWed, 07 Jul 2010 01:01:12 GMTtim.pinderhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxIt is not mainly for comma seperated column name. It can be use when you want comma seperated data on grouping of some other field. Please see the output resultset 1 and resultset 2.Tue, 06 Jul 2010 22:49:19 GMTNitin Bhargavahttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxMine isn't nearly as fancy, and it sticks a dangling comma on the end, but when I just want a quick listing of all columns in a table (say, for a select statement where they are in Ordinal Position order), I use the following (just change the &lt;tablenamehere&gt; to a real table name):DECLARE @colline varchar(4000)SET @colline = ''SELECT @colline=@colline+COLUMN_NAME + ',' FROM information_schema.columnsWHERE TABLE_NAME = '&lt;tablenamehere&gt;'ORDER BY ORDINAL_POSITIONSELECT @collineTue, 06 Jul 2010 13:51:12 GMTvikingDBAhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxVirtuoso coding, certainly, but why not take advantage of SQL Server's ability to create custom aggregation functions? Microsoft's example can be found here:[url=http://msdn.microsoft.com/en-us/library/ms182741.aspx]http://msdn.microsoft.com/en-us/library/ms182741.aspx[/url]Tue, 06 Jul 2010 09:09:39 GMTRobert.Smith-1001156http://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxTim, I had the same problem. I just removed all the offending whitespace and it works fine now:[code="sql"]----------------------------------------------------------------------- Create Table and Insert data---------------------------------------------------------------------if object_id('tempdb..TestTable')is not null drop table TestTableCreate Table TestTable (RowID Int Identity(1, 1), Col1 int, Col2 char(3))Insert TestTable Values (1, 'ABC') Insert TestTable Values (1, 'DEF') Insert TestTable Values (1, 'GHI') Insert TestTable Values (2, 'JKL') Insert TestTable Values (2, 'MNO') Insert TestTable Values (3, 'PQR') Insert TestTable Values (3, 'STU') select * from TestTable----------------------------------------------------------------------- Add TestTable column and Insert Row Number---------------------------------------------------------------------Alter Table TestTable Add ColRowNo intUpdate TestTable Set ColRowNo = RowNoFrom (Select RowID , Row_Number() Over (Partition By Col1 Order by RowID) RowNo From TestTable)as B Where TestTable.RowID = B.RowID;----------------------------------------------------------------------- Concatenate Col2 in comma seperated form Group by Col1 ---------------------------------------------------------------------With CTE (X, Y, Z) AS(Select Col1, Convert(Varchar,Col2), ColRowNo From TestTable Where ColRowNo = 1Union AllSelect Col1, Convert(Varchar, Y + ',' + Convert(Varchar, Col2)) Y, ColRowNo From TestTable Inner Join CTE on Col1 = X and ColRowNo = Z + 1)Select X Col1, Y [Concatenated Col2] From CTEInner Join (Select Col1, Max(ColRowNo) ColRowNo From TestTable Group by Col1) TestTable on X = Col1 and Z = ColRowNo Order by XOPTION (MAXRECURSION 2);Drop Table TestTable GO[/code]Tue, 06 Jul 2010 09:02:44 GMTWesley Nortonhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxI'm getting a different error:[code="sql"]Update TestTable Set ColRowNo = RowNoFrom (    Select RowID, Row_Number() Over (Partition By Col1 Order by RowID) RowNo From TestTable) B Where TestTable.RowID = B.RowID;[/code]gives this:Msg 102, Level 15, State 1, Line 5Incorrect syntax near ' '.Msg 102, Level 15, State 1, Line 6Incorrect syntax near ')'.:(Tue, 06 Jul 2010 07:24:32 GMTtim.pinderhttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxPlease check if following statement is missing from your script - Alter Table TestTable Add ColRowNo intif it is missing, then add it next to statement "select * from TestTable"it will work.Tue, 06 Jul 2010 03:23:35 GMTNitin Bhargavahttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxYour code is not working. It 's giving error ColRowNo is invalid Column. Can you please try to remove that error from your code. I executed that code in sql server 2008Tue, 06 Jul 2010 02:30:33 GMTricky70ranahttp://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxHere's a slightly more succinct version inspired by code from this article [url]http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[/url][code="sql"]SELECT t1.col1,( SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY col1, col2)=1 THEN '' ELSE ',' END + col2 FROM TestTable WHERE col1 = t1.col1 ORDER BY col1 ,col2 FOR XML PATH('') )FROM TestTable t1GROUP BY col1[/code]Tue, 06 Jul 2010 02:07:04 GMTnigel.http://www.sqlservercentral.com/Forums/Topic947687-2732-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/T-SQL/70483/">Get concatenated value for a column without cursor</A>[/B]Mon, 05 Jul 2010 21:57:34 GMTNitin Bhargava