如何返回具有串联标识符的列

您可以聚合结果并计算 tablename ...

http://sqlfiddle.com/#!18/dc20c/32

SELECT
    MAX(CASE WHEN tablename = 'A' THEN 'A' ELSE '' END)
  + MAX(CASE WHEN tablename = 'B' THEN 'B' ELSE '' END)
  + MAX(CASE WHEN tablename = 'Z' THEN 'Z' ELSE '' END)  AS tablename,
  column1,
  column2
FROM
(
  SELECT
    'A' AS tablename,
    Column1,
    Column2
  FROM
    TableFoo

UNION ALL

  SELECT
    'B' AS tablename,
    Column1,
    Column2
  FROM
    TableBar
)
  TableFooBar
GROUP BY
  column1,
  column2
;

注意,我还使用 UNION ALL 而不是 UNION .

联合体 在删除重复项时有额外的开销。这可能会以您不希望的方式影响您的结果,并且肯定会带来额外的处理成本。

联合所有 避免这两种情况。

试试这个

;WITH Q
AS
(
  SELECT
  'A' AS tablename,
  Column1,
  Column2
FROM
  TableFoo

  UNION

  SELECT
  'B' AS tablename,
  Column1,
  Column2
FROM
  TableBar

)
SELECT DISTINCT
  TableName = replace(STUFF((SELECT ','+TableName  AS [data()]
                     FROM Q T2 
                     WHERE T2.Column1 = T1.Column1
                        AND T2.Column2 = T1.Column2
                     FOR XML PATH('')),1,1,''),',','') ,
  Column1,
  Column2
  FROM Q T1

检查 SQL Fiddle Demo

您可以通过一个没有嵌套的联合来实现这一点。

SELECT
  CASE WHEN (A.COLUMN1 IS NOT NULL AND A.COLUMN2 IS NOT NULL) THEN 'AB' ELSE 'A' END AS tablename,
  B.Column1,
  B.Column2
FROM
  TableFoo B
   LEFT OUTER JOIN TABLEBAR A ON A.COLUMN1=B.COLUMN1 AND A.COLUMN2=B.COLUMN2
  UNION
SELECT
  'B' tablename,
  B.Column1,
  B.Column2
FROM
  TableBar B
      LEFT OUTER JOIN TABLEFOO A ON A.COLUMN1=B.COLUMN1 AND A.COLUMN2=B.COLUMN2
WHERE (A.COLUMN1 IS NULL AND A.COLUMN2 IS NULL)