String result set together

• cheshirefox

  Hall of Fame

  Points: 3464

  More actions

  April 21, 2011 at 9:27 am

  #238116

  I have a query with a simple result set of 2 rows.

  Query - select ColA from TableA

  ColA

  1

  2

  I want the result set to actually be:

  12

  My query:

  SELECT CAST((SELECT CAST(ColA AS NVARCHAR(3))

  FROM dbo.TableA WITH(NOLOCK)

  FOR XML PATH ('')) AS NVARCHAR(MAX))

  Is this is easiest way to generate the specific result set?

  Thanks in advance.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738632

  More actions

  April 21, 2011 at 9:50 am

  #1314591

  do you mean the result set is both rows in one field or row? This is a cross tab. Or a PIVOT.

  Simply, with your example, you can do:

  select a.ColA + b.ColA

  from TableA a

  inner join TableB b