SQL Query Help

Transact-SQL
1

Hi Guys,

Need help here. I am working on Project in SSRS where users can run and execute the standard report. One user report for all users and users can Hide and display Columns/Field name change. I was thinking
If I load the data into table and display from there. Please see below sample code

CREATE TABLE #Data
(
ID INT IDENTITY(1,1),
NAME VARCHAR(50),
DATE VARCHAR(10),
City VARCHAR(15)
)

CREATE TABLE #Display
(
ID INT IDENTITY(1,1),
Display VARCHAR(50),
ColumnName VARCHAR(10)
)

INSERT INTO #Data
( NAME, DATE, City)
VALUES
( 'Mr John Smith','2005-05-20','Chicago'),
( 'Mr.Smith', '2014-01-13','New York'),
( 'ACME Social Club', '2012-12-01', 'Los Angles')

INSERT INTO #Display
( Display, ColumnName )
VALUES
('YES', 'FullName'),
('NO' , NULL),
('YES', 'GoodCity')

SELECT * FROM #Data
SELECT * FROM #Display

Here IS what I want the END RESULT

ID FullName GoodCity
1 Mr John Smith Chicago
2 Mr.Smith New York
3 ACME Social Club Los Angles

Any advice would be greatly appreciated.
Thank You

2 
CREATE TABLE ##Data
   (
      ID       int IDENTITY(1, 1)
    , FullName varchar(50)
    , DataDate varchar(10)
    , City     varchar(15)
   );
GO
CREATE TABLE #Display
   (
      DisplayOrder int IDENTITY
    , Display      bit
    , ColumnName   varchar(10)
   );
GO
INSERT INTO ##Data
   (
      FullName
    , DataDate
    , City
   )
VALUES
   ( 'Mr John Smith', '2005-05-20', 'Chicago' )
 , ( 'Mr.Smith', '2014-01-13', 'New York' )
 , ( 'ACME Social Club', '2012-12-01', 'Los Angles' );
GO
INSERT INTO
   #Display
   (
      Display
    , ColumnName
   )
VALUES
   ( 1, 'FullName' )
 , ( 0, 'Good' )
 , ( 1, 'City' );
GO

DECLARE @sql varchar(8000) = 'SELECT ';
WITH
   Jic
AS
   (
      SELECT
         Row_Number() OVER (ORDER BY DisplayOrder) n
       , ColumnName
      FROM
         #Display
      WHERE
         Display = 1
   )
SELECT
   @sql += CASE WHEN Jic.n = 1 THEN '
 '            ELSE '
 , '       END + Jic.ColumnName
FROM
   Jic;

SET @sql += '
FROM ##Data;';
EXEC (@sql);

DROP TABLE ##Data;
DROP TABLE #Display;

image

3

I like Joseph's solution.

If you are not familiar with Dynamic SQL then make sure you are aware of, and watch out for, "SQL Injection" - particularly once you start to add a WHERE clause, with parameters, to the code.