Query column headings data in SQL

Question

I need to display column headings that occur in a different table as a list.

Example:

   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

How would I check if the column headings are equal to any in the "required" list and then display only those?

Why are your column names the names of people? This feels like a design flaw; have a column for the name, not one column for each oerson — Thom A, Aug 14 '20 at 13:11
What you are describing requires dynamic SQL. And you don't specify what to do if the column name is *not* in the table. — Gordon Linoff, Aug 14 '20 at 13:14
@Larnu The values for each person per row is important data for this specific table — nix, Aug 14 '20 at 14:07
@GordonLinoff Thanks. Will look into dynamic SQL. If the column name is not in the table, then in need not be displayed — nix, Aug 14 '20 at 14:08
I didn't say the information for the user *wasn't* important, but they should not have 1 column per user, it should be 1 row per user. Having a design like this doesn't scale. Each time a user is added you would have to add a new column to your table, and then edit any queries that reference said table(s) to show the new column. You would also need to `DROP` the column when a user is removed. Have a row per user, and you don't need to keep changing the definition of your table(s). — Thom A, Aug 14 '20 at 14:18
@Larnu I see. Thanks. I understand. Unfortunately, it's a table that the result of an export so I am not really able to change that design. — nix, Aug 14 '20 at 15:11
@Larnu Turns out that the table is the result of a pivot so it's actually a summary. I managed to query the original table. — nix, Aug 31 '20 at 13:19

score 0 · Answer 1 · answered Aug 14 '20 at 18:28

I recommend you make use of Pivot and Dynamic SQL. Join from your complete names table to the required names table to end up with just the names you need as columns. Format those values to be later used in pivot with dynamic SQL. With pivot you are forced to aggregate something. Look at the Pivot rows to columns without aggregate post for more information. I hard coded 1 as the aggregate value we need to be able to pivot. You can ignore that value and join on name assuming that is your key to other tables to get additional data for each name.

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

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

Pivot rows to columns without aggregate

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

Query column headings data in SQL

1 Answers1