InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 04:28:20 GMT20http://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxsomething along the lines of...[code="sql"]Declare @assignTo nvarchar(4000) select @assignTo = Coalesce(@assignTo + ', ', '') + CAST(Name as nvarchar(250)) from table2 where ... Select @assignTo[/code]but fair warning that won't work in a subquery..to use it in a correlated subquery you'll have to write it into a UDF with table access.Umm....as far as I know. It's been a while.Wed, 06 Mar 2013 14:35:32 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxWhat code should I use in SQL 2000 then?Wed, 06 Mar 2013 13:19:27 GMTcaroline.keihnhttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxFor XML Path is not supported in sql 2000.Wed, 06 Mar 2013 13:06:52 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxHello,I tried this code in SQL 2000 but get this error: "Incorrect syntax near the keyword 'FOR'." Why is that?IF OBJECT_ID('TEMPDB..#IF_BestPriceMfg2') IS NOT NULLDROP TABLE #IF_BestPriceMfg2CREATE TABLE #IF_BestPriceMfg2( GlobalCustomer INT, Productline_ID VARCHAR(6))INSERT INTO #IF_BestPriceMfg2SELECT GlobalCustomer ,Productline_IDFROM #IF_BestPriceMfgGROUP BY GlobalCustomer ,Productline_IDSELECT * FROM #IF_BestPriceMfg2SELECT p1.GlobalCustomer, ( SELECT Productline_ID + ',' FROM #IF_BestPriceMfg2 p2 WHERE p2.GlobalCustomer = p1.GlobalCustomer ORDER BY Productline_ID FOR XML PATH('') ) AS Concat_ValuesFROM #IF_BestPriceMfg2 p1GROUP BY p1.GlobalCustomer ;Wed, 06 Mar 2013 12:58:21 GMTcaroline.keihnhttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxThanks to everybody!!!; I was testing this script in SQL Server 2005 and everthing is ok.//>>>>DECLARE @lValueStr VARCHAR(MAX)SET @lValueStr = NULLSELECT @lValueStr = COALESCE(@lValueStr + ', ', '') + a.Cod_Asignatura + '(' + CAST(rtrim(ltrim(b.Des_Asignatura)) AS VARCHAR(30)) + ')'FROM pla_tt_PlanificacionDetAsig a inner join pla_tc_Asignatura bon a.cod_empresa = b.cod_empresaand a.cod_recinto = b.cod_recintoand a.cod_asignatura = b.cod_asignaturaSELECT @lValueStr<<<<//I Really don't understan how it work, but just work and good! :-) :w00t:Best regards.Sat, 08 Jan 2011 02:06:13 GMTEarl Downshttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxThis might be helpful for some who accidentally tumbles upon this thread :-D[code="sql"]IF OBJECT_ID('TEMPDB..#CONCAT_COLUMN_VALUES') IS NOT NULLDROP TABLE #CONCAT_COLUMN_VALUESCREATE TABLE #CONCAT_COLUMN_VALUES( GROUP_ID INT, COL_VAL VARCHAR(5))INSERT INTO #CONCAT_COLUMN_VALUESSELECT 1, 'A' UNION ALLSELECT 1, 'B' UNION ALLSELECT 1, 'C' UNION ALLSELECT 2, 'D' UNION ALLSELECT 2, 'E' UNION ALLSELECT 3, 'F' UNION ALLSELECT 4, 'G'SELECT * FROM #CONCAT_COLUMN_VALUESSELECT p1.GROUP_ID, ( SELECT COL_VAL + ',' FROM #CONCAT_COLUMN_VALUES p2 WHERE p2.GROUP_ID = p1.GROUP_ID ORDER BY COL_VAL FOR XML PATH('') ) AS Concat_ValuesFROM #CONCAT_COLUMN_VALUES p1GROUP BY p1.GROUP_ID ;[/code]Cheers!!Thu, 22 Apr 2010 06:48:34 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxI had already done a PIVOT when I saw you'd already got it! [code="sql"];WITH ctePrepareAS(SELECT id, name, name + ' (' + CONVERT(VARCHAR, counts) + ')' AS NameWithCount FROM test)SELECT id, Cooking + ', ' + Literature + ', ' + Math + ', ' + Reading AS categorycountsFROM(SELECT id, name, NameWithCount FROM ctePrepare) AS ZPIVOT (MAX(NameWithCount) FOR name IN (Cooking, Literature, Math, Reading)) AS pvt[/code]Thu, 22 Apr 2010 04:25:36 GMTsteve-893342http://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxHi,Try this...--------------------------------------------------------DECLARE @lValueStr VARCHAR(MAX)SET @lValueStr = NULLSELECT @lValueStr = COALESCE(@lValueStr + ', ', '') + name + '(' + CAST(Counts AS VARCHAR(5)) + ')' FROM Test TSELECT @lValueStr---------------------------------------------------------Shaiju C.K.Thu, 22 Apr 2010 01:04:30 GMTC.K.Shaijuhttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxlol, nevermind it too)))))))))))))Thu, 22 Apr 2010 00:13:33 GMTbtio_3000http://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxYes, but can you - or anyone - share your wisdom?Let's say there is a table like belowKeyField..............DescField1....................... 'A'1....................... 'B'1....................... 'C'2.......................'D'2.......................'E'2.......................'F'and I need a single select returning1.........'A,B,C'2.........'D,E,F'?Thank youWed, 21 Apr 2010 17:03:30 GMTbtio_3000http://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxNever mind, i got it.Wed, 02 Sep 2009 13:28:42 GMTsqlbluehttp://www.sqlservercentral.com/Forums/Topic781712-1292-1.aspxHi, I have the below table, and I need to combine theminto one records dynamically (I'm not sure I'm clear on this,so maybe you look at the below table is better).create table Test( id int, name varchar(30), counts int)insert into Testselect 1, 'Math', 5unionselect 1, 'Literature', 3unionselect 1, 'Cooking', 1unionselect 1, 'Reading', 2I would like the result to look like this below, id categorycounts1 Cooking (1), Literature (3), Math(5), Reading(2) the challenge I have is the id can have multiple values like this, how do I know to combine them all dynamically and correctly. thanks a lot if you could help and give any input.Wed, 02 Sep 2009 12:20:13 GMTsqlblue