SQL查询从另一个表中检索每列的所有值？

Question

我有大约200列的表，所以我想知道下面是否有可能：

考虑下表，我们称之为PersonInformation：

PersonId AgeCode NeighbourhoodCode DogFlagCode ... 
1  5  8     10 
2  1  9     11 
3  5  8     10 

考虑其他表，让我们称之为InformationValues

Id  Value 
5  21 
1  22 
8  Neighbourhood A 
9  Neighbourhood B 
10  Dog present 
11  Dog not present 

现在我想做的就是创建一个视图，显示这一点：

PersonId AgeCode NeighbourhoodCode DogFlagCode ... 
1  21  Neighbourhood A Dog present 
2  22  Neighbourhood B Dog not present 
3  21  Neighbourhood A Dog present 

由于有大约200列可以使用Microsoft SQL Server Management Studio使用生成脚本来执行此操作吗？或者这是可能的普通SQL？

这里是棘手的部分：一些表格不具备代码后缀不能被搜索和它们的值列。

我一直在努力研究这个问题，但除了手动完成之外，我找不到任何其他的东西。

更新1 至于我手动定义为“手动输入所有列名称”之前的句子。

感谢

Answer 1

我不知道，如果是这样的一种有效方式。有可能有其他方法可以有效实现。但现在我可以考虑为此创建函数。这是

CREATE FUNCTION GetCodeValue(@Id int) 
RETURNS VARCHAR(100) 
AS 
    BEGIN 
     DECLARE @value varchar(100) 
     SET @value = (SELECT Value from InformationValues WHERE [email protected]) 
     return @value; 
    END 

现在写你的查询来获取这样

SELECT PersonId, 
GetCodeValue(AgeCode) as AgeCode, 
GetCodeValue(NeighbourhoodCode) as AgeCode, 
GetCodeValue(DogFlagCode) as DogFlagCode 
FROM PersonInformation 

Answer 2

这是join秒的数量庞大的输出，给定的数据设置方式。这个想法是：

select pi.PersonId, iv_age.value as age, 
     iv_nc.value as Neighbourhood, 
     iv_df as DogFlag 
from PersonInformation pi left join 
    InformationValues iv_age 
    on pi.AgeCode = iv_age.id left join 
    InformationValues iv_nc 
    on pi.NeighbourhoodCode = iv_nc.id left join 
    InformationValues iv_df 
    on pi.DogFlagCode = iv_df.id; 

您应该能够继续添加每个列的连接和添加标志。

键入所有这些都是一种痛苦。您可以使用information_schema.columns表获取列的列表，并使用SQL语句或将名称放入电子表格并使用电子表格公式生成代码来生成代码。

SQL Server可以处理如此长的查询和表的数量。你可以通过在select之前放置create view <viewname> as来将其放入视图。

Answer 3

你可以使用下面的脚本来生成并执行SELECT声明：

-- Script parameters 
DECLARE @MainTable NVARCHAR(256) 
SET @MainTable = 'dbo.PersonInformation' -- This must be the full name (including the schema name) 
-- End if Script parameters 


DECLARE @Columns NVARCHAR(MAX), @Tables NVARCHAR(MAX) 
SELECT @Columns = '', @Tables = @MainTable + ' maint'; -- Main table 

SELECT @Columns = @Columns + 
     CASE 
      WHEN col.name LIKE '%Code' THEN 
       N', alias' + CONVERT(NVARCHAR(11), col.column_id) + '.[Value] AS ' + QUOTENAME(col.name) 
      ELSE ', maint.' + QUOTENAME(col.name) 
     END, 
     @Tables = @Tables + 
     CASE 
      WHEN col.name LIKE '%Code' THEN 
       CHAR(13) + CHAR(10) + 
       N'LEFT JOIN dbo.InformationValues AS alias' + CONVERT(NVARCHAR(11), col.column_id) + 
       N' ON alias' + CONVERT(NVARCHAR(11), col.column_id) + N'.Id = maint.' + QUOTENAME(col.name) 
      ELSE N'' 
     END 
FROM sys.columns col 
JOIN sys.tables t ON col.object_id = t.object_id 
JOIN sys.schemas s ON t.schema_id = s.schema_id 
WHERE s.name = PARSENAME(@MainTable,2) -- Schema Name 
AND  t.name = PARSENAME(@MainTable,1) -- Table Name 
ORDER BY col.column_id; 

SET @Columns = STUFF(@Columns, 1, 1, N''); 

DECLARE @SqlStatement NVARCHAR(MAX); 
SET @SqlStatement = 
    N'SELECT ' + @Columns + CHAR(13) + CHAR(10) + 
    N'FROM ' + @Tables + 
    N'--WHERE '; -- Maybe you have some filtering conditions 

PRINT @SqlStatement 
-- EXEC(@SqlStatement) 

上面的脚本会产生像下面的例子SELECT声明：

SELECT alias1.[Value] AS [ProductCode], 
    maint.[Name], 
    maint.[ProductNumber], 
    maint.[MakeFlag], 
    maint.[FinishedGoodsFlag], 
    maint.[Color], 
    maint.[Class], 
    maint.[Style], 
    alias19.[Value] AS [ProductSubcategoryCode], 
    alias20.[Value] AS [ProductModelCode], 
    maint.[SellStartDate], 
    maint.[SellEndDate], 
    maint.[DiscontinuedDate], 
    alias24.[Value] AS [rowguCode], 
    maint.[ModifiedDate] 
FROM Production.Product maint 
LEFT JOIN dbo.InformationValues AS alias1 ON alias1.Id = maint.[ProductCode] 
LEFT JOIN dbo.InformationValues AS alias19 ON alias19.Id = maint.[ProductSubcategoryCode] 
LEFT JOIN dbo.InformationValues AS alias20 ON alias20.Id = maint.[ProductModelCode] 
LEFT JOIN dbo.InformationValues AS alias24 ON alias24.Id = maint.[rowguCode]--WHERE 

Answer 4

你可能想在这里使用一个Common Table Expression，当然是因为你必须多次参考同一张表。

我必须承认你的问题让我困惑 - 尤其是最后的声明。我得到的印象是你想要一个递归查找，如果没有人工定义，这是不可能的，毕竟你怎么定义想要你的链接和脚本的数据？

CTE的使用应该会给你提供比标准的重复连接好得多的性能，它也是一个小清洁器。这里有一个简单的例子：

with CTE as (
    select id, value from cte_data 
) 

select 
    T.Name 
    ,A.Value 
    ,B.Value 
    ,C.Value 
from cte_test T 
    inner join CTE A on A.Id = T.Val1 
    inner join CTE B on B.Id = T.Val2 
    inner join CTE C on C.Id = T.Val3 

因此，要翻译成你的例子：

with CTE as (
    select id, value from InformationValues 
) 

select 
    PInfo.PersonId 
    ,PInfo.AgeCode 
    ,A.Value as NeighbourhoodCode 
    ,B.Value as DogFlagCode 
from PersonInformation PInfo 
    inner join CTE A on A.Id = PInfo.NeighbourhoodCode 
    inner join CTE B on B.Id = PInfo.DogFlagCode