在SQL中查询列标题数据

Question

我需要将不同表中出现的列标题显示为列表。

示例：

Column headings - Adam, Cory, Jack, Jane, John, Josef, Mary, Timothy, Charlotte, Jessica, Kristal, Clive
Required column headings (contained within another table) - Jack, Jane, John, Mary, Maria, Josef

我该如何检查列标题是否等于"；"必需"；列出，然后只显示这些？

Answer 1

我建议您使用Pivot和Dynamic SQL。从完整的名称表连接到所需的名称表，最终只得到所需的列名称。设置这些值的格式，以便以后使用动态SQL在数据透视中使用。有了pivot，你就不得不聚合一些东西。有关详细信息，请查看"将行数据透视到没有聚合帖子的列"。我把1硬编码为我们需要的总值，以便能够进行转换。您可以忽略该值，并在名称上联接，假设它是其他表的键，以便为每个名称获取额外的数据。

https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-服务器-ver15

https://learn.microsoft.com/en-us/sql/odbc/reference/dynamic-sql?view=sql-服务器-ver15

将行透视到没有聚合的列

declare @PivotColumns nvarchar(max) = ''
create table #required ( name nvarchar(500) )
insert into #required ([name])
select 'Jack' union select 'Jane' union select 'John' union select  'Mary' union select 'Maria' union select 'Josef'
create table #allnames ( name nvarchar(500) )
insert into #allnames ([name])
select 'Adam' union select 'Cory' union select 'Jack' union select 'Jane' union select 'John' union select 'Josef' union select 'Mary' union select 'Timothy' union select 'Charlotte' union select 'Jessica' union select 'Kristal' union select 'Clive'  union select 'Maria'
select @PivotColumns = @PivotColumns + QUOTENAME([name]) + ',' from #required
select @PivotColumns = substring(@PivotColumns,1,len(@PivotColumns)-1)
declare @SQL nvarchar(max) = ''
select @SQL = N'
select
*
from (
select
req.*, 1 [Val]
from #allnames as [All]
join #required as Req
on [All].[name] = Req.[name]
) src
pivot
(
max([Val])
FOR [name] in ('+@PivotColumns+')
) piv';

exec sp_executesql @SQL
drop table #required
drop table #allnames