Select columns from a table based on the column names from another table

Question

I want to select data from following tables for a Jasper report.

AA_COLUMN_NAMES

AA_PAYMENT_DETAILS

DDL + DML

CREATE TABLE SLPAYROLL_LIVE.dbo.AA_COLUMN_NAMES(
    id int PRIMARY KEY NOT NULL,
    name varchar(500) NOT NULL,
    active bit
);
INSERT INTO SLPAYROLL_LIVE.dbo.AA_COLUMN_NAMES (id, name, active) VALUES (1, 'col_1', 1);
INSERT INTO SLPAYROLL_LIVE.dbo.AA_COLUMN_NAMES (id, name, active) VALUES (2, 'col_2', 1);
INSERT INTO SLPAYROLL_LIVE.dbo.AA_COLUMN_NAMES (id, name, active) VALUES (3, 'col_3', 1);
INSERT INTO SLPAYROLL_LIVE.dbo.AA_COLUMN_NAMES (id, name, active) VALUES (4, 'col_4', 0);
CREATE TABLE SLPAYROLL_LIVE.dbo.AA_PAYMENT_DETAILS(
    id int PRIMARY KEY NOT NULL,
    username varchar(500),
    col_1 varchar(500),
    col_2 varchar(500),
    col_3 varchar(500),
    col_4 varchar(500)
);
INSERT INTO SLPAYROLL_LIVE.dbo.AA_PAYMENT_DETAILS (id, username, col_1, col_2, col_3, col_4) VALUES (1, 'chathura', '500', '200', '300', '0');
INSERT INTO SLPAYROLL_LIVE.dbo.AA_PAYMENT_DETAILS (id, username, col_1, col_2, col_3, col_4) VALUES (2, 'gihan', '300', '100', '100', '0');

I want to select only active columns from AA_PAYMENT_DETAILS. The active column names can be get from the AA_COLUMN_NAMES table.

I have googled my question and found the following solution.

Select columns from one table based on the column names from another table

Since I want to use that query in a Jasper Report, above solution does not worked. Does anyone know a solution?

Sample data is best served as [DDL](https://en.wikipedia.org/wiki/Data_definition_language) + [DML](https://en.wikipedia.org/wiki/Data_manipulation_language). Please [edit] your question to include it, your current attempt and your desired results. For more details, [read this.](https://dba.meta.stackexchange.com/questions/2976/help-me-write-this-query-in-sql) — Zohar Peled, Oct 16 '18 at 10:37
can someone explain why down-voted my question without answering or explaining a proper reason? — Chathura Buddhika, Oct 17 '18 at 04:42
I don't know who downvoted you or why. I've upvoted now that your question contains proper sample data. I don't know if I'll have the time today to post an answer, though. — Zohar Peled, Oct 17 '18 at 07:25
@ZoharPeled can you please post an answer when you have a time — Chathura Buddhika, Oct 17 '18 at 12:22

score 1 · Accepted Answer · answered Oct 18 '18 at 09:35

You have to use dynamic SQL to do this - here is one fairly simple way:

DECLARE @SQL nvarchar(4000) = '';

SELECT @SQL = @SQL +', '+ name 
FROM dbo.AA_COLUMN_NAMES
WHERE active = 1

SET @SQL = 'SELECT id, username' + @SQL + ' FROM dbo.AA_PAYMENT_DETAILS'

EXEC(@SQL)

Select columns from a table based on the column names from another table

1 Answers1