SQL Query Help

• rocky_498

  SSCertifiable

  Points: 6532

  More actions

  October 16, 2017 at 3:01 pm

  #339900

  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

  IDFullNameGoodCity
  1Mr John SmithChicago
  2Mr.SmithNew York
  3ACME Social ClubLos Angles

  Any advice would be greatly appreciated.
  Thank You

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  October 16, 2017 at 3:40 pm

  #1963939

  
CREATE TABLE ##Data
   (
      ID       int IDENTITY(1, 1)
    , FullName varchar(50)
    , DataDate date
    , 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;