将列添加到SQL查询结果中

I'm putting together a report in SSRS. The dataset is populated with a SQL query of an MS SQL server. It's querying several similar tables using Union All. The problem is that there's some information loss. The different tables are for different worksites, but none of the columns in those tables has the name of the site; the only way to identify a site is by the table name. In the combined columns which are the result of the Union All, there's no way to tell which rows come from which site.

我在SSRS中汇总了一份报告。数据集填充了MS SQL服务器的SQL查询。它使用Union All查询几个类似的表。问题是有一些信息丢失。不同的表适用于不同的工作场所，但这些表中没有列具有站点的名称;识别站点的唯一方法是使用表名。在作为Union All结果的组合列中，无法确定哪些行来自哪个站点。

Is there a way to alter my query to add a column to the results, which would have the worksite with which each row is associated? I can't add this to the original table, because I have read-only permissions. I'd thought of something like this, but I don't know what sort of expression to use, or if it can even be done:

有没有办法改变我的查询，以便为结果添加一个列，哪个工作点与每一行相关联？我无法将其添加到原始表中，因为我具有只读权限。我想过这样的事情，但我不知道使用什么样的表达式，或者甚至可以做到：

SELECT t1.column, t1.column2
FROM t1
<some expression>
UNION ALL
SELECT t2.column, t2.column2
FROM t2
<some expression>
UNION ALL
...

etc. The expression would 'add' a column, which would add the site name associated with each part of the query. Could this or anything else work to get the site name?

表达式将“添加”一列，该列将添加与查询的每个部分关联的站点名称。这个或其他什么方法可以获得网站名称？

2 个解决方案

#1

Manually add it when you build the query:

在构建查询时手动添加它：

SELECT 'Site1' AS SiteName, t1.column, t1.column2
FROM t1

UNION ALL
SELECT 'Site2' AS SiteName, t2.column, t2.column2
FROM t2

UNION ALL
...

EXAMPLE:

例：

DECLARE @t1 TABLE (column1 int, column2 nvarchar(1))
DECLARE @t2 TABLE (column1 int, column2 nvarchar(1))

INSERT INTO @t1
SELECT 1, 'a'
UNION SELECT 2, 'b'

INSERT INTO @t2
SELECT 3, 'c'
UNION SELECT 4, 'd'


SELECT 'Site1' AS SiteName, t1.column1, t1.column2
FROM @t1 t1

UNION ALL
SELECT 'Site2' AS SiteName, t2.column1, t2.column2
FROM @t2 t2

RESULT:

结果：

SiteName  column1  column2
Site1       1      a
Site1       2      b
Site2       3      c
Site2       4      d

#2

why dont you add a "source" column to each of the queries with a static value like

为什么不为每个具有静态值的查询添加“源”列

select 'source 1' as Source, column1, column2...
from table1

UNION ALL

select 'source 2' as Source, column1, column2...
from table2

#1