How do I show the output like (2000,2001)

• Ajdba

  Ten Centuries

  Points: 1051

  More actions

  August 27, 2012 at 2:34 pm

  #280793

  Hi

  I have years value populating as below:

  SELECT @Years = (SELECT STUFF((SELECT ', ' + [Year] FROM (SELECT [Year] FROM #Years) AS T FOR XML PATH('')),1,1,''))

  How do I show the output like this : (2000,2001)

  Thanks

• Bill Talada

  SSChampion

  Points: 11954

  More actions

  August 27, 2012 at 2:43 pm

  #1530520

  declare @Years table (ccyy varchar(4));

  insert into @Years values (2010);

  insert into @Years values (2011);

  insert into @Years values (2012);

  declare @Y varchar(max);

  SET @Y = '('+(SELECT STUFF((SELECT ', ' + ccyy FROM (SELECT ccyy FROM @Years) AS T FOR XML PATH('')),1,1,''))+')'

  print @y;

• Ajdba

  Ten Centuries

  Points: 1051

  More actions

  August 27, 2012 at 2:58 pm

  #1530531

  I have already a table where the years gets inserted and I would like to parse it to show like this: (2000,2001)

  CREATE TABLE #Years (Year varchar(6))

  INSERT #Years

  (Year)

  SELECT '''' + CAST(Node.query('text()') AS varchar(100)) + '''' as YEAR

  FROM @XMLSelectedYears.nodes('/Years/*/YYYY') tempxml (Node);

  TIA

• Ajdba

  Ten Centuries

  Points: 1051

  More actions

  August 27, 2012 at 3:07 pm

  #1530535

  One more thing I need to add that in the #year table, the years get inserted like this:

  '2000', '2001','2002'

  All I need to do is to take out the tick around the year as below:

  2000,2001,2002

• Ajdba

  Ten Centuries

  Points: 1051

  More actions

  August 27, 2012 at 3:39 pm

  #1530546

  Can I use REPLACE function to get rid of quotation mark around the year