Doing a select with dynamic columns

Question

Post reply

Doing a select with dynamic columns

astrid 69000

Hall of Fame

Points: 3208
More actions
June 17, 2021 at 2:03 pm
Go to Answer
#3897475
Hi,
I have a query that produce a table where I have a list of columns.
Then I choose check what columns are on the table excluding the column from the other table.
```
select COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'CustomerV3_ParentAccount'
AND COLUMN_NAME NOT IN (SELECT DISTINCT ColumnName FROM ColumnName WHERE TableName = 'CustomerV3_ParentAccount')
```
my question is, how can I do a select from CustomerV3_ParentAccount using the results I got from that query.
I am looking to do it dynamically as the columns keep changing on the table ColumnName
thanks
astrid

Thom A

SSC Guru

Points: 99133

June 17, 2021 at 3:09 pm

Answer

#3897517

Dynamic SQL is the only way to go, as Phil says. So one way would be like so:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter nvarchar(20) = N',' + @CRLF + N'       '

SELECT @SQL = N'SELECT ' +
              STRING_AGG(QUOTENAME(c.[name]),@Delimiter) + @CRLF +
              N'FROM dbo.CustomerV3_ParentAccount;'
FROM sys.tables t --I prefer the sys objects
     JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.[name] = N'CustomerV3_ParentAccount'
  AND NOT EXISTS (SELECT 1
                  FROM dbo.ColumnName CN
                  WHERE CN.TableName = t.[name]
                    AND VN.ColumnName = c.[name]);

--PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

As the column names are not static, doing this dynamically is probably the only way you will get this to work.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 2

thanks, i needed to do it dynamically, i was just not sure how to go about it.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

>> I have a query that produce a table where I have a list of columns. Then I choose check what columns are on the table excluding the column from the other table. <<

No. I worked on the committee that define this language. The tables in the column are known as part of the definition of the table! You missed one of the fundamental concept of RDBMS. It goes back to Leibniz and the concept and logic that an entity is the sum of all of its attributes. What you seem to want to do is to use SQL for something we never intended for it to be used for. We don't format data in the database tier of a tiered architecture! We pass the raw data to a presentation/computation layer, and that does whatever needs to be done for display purposes.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

jcelko212 32090 wrote:

>> I have a query that produce a table where I have a list of columns. Then I choose check what columns are on the table excluding the column from the other table. <<
No. I worked on the committee that define this language. The tables in the column are known as part of the definition of the table! You missed one of the fundamental concept of RDBMS. It goes back to Leibniz and the concept and logic that an entity is the sum of all of its attributes. What you seem to want to do is to use SQL for something we never intended for it to be used for. We don't format data in the database tier of a tiered architecture! We pass the raw data to a presentation/computation layer, and that does whatever needs to be done for display purposes.

It's not always done for reporting purposes, Joe. Sometimes is to convert an EAV into a proper table structure. And, sometimes it IS for reporting... like when there's no reporting app in sight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)