Dynamic SQL Column Selection

Question

I currently have a table filled with Data for multiple days. These dates are set as Columns within the data table and therefore i am required to unpivot the table to perform relevant queries.

What is the easiest way to select all columns beginning with 2020%

I have used the following Q&A Selecting all columns that start with XXX using a wildcard? In order to create a separate Table with all the columns i require

SELECT [COLUMN_NAME]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'Table_Name'
AND [COLUMN_NAME] LIKE '2020%'
GO

However unsure how to use this secondary table to simplify my original question.

Have a read up on [prepared statements](https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html). They can get a bit awkward to do in SQL so you might one to assemble the query in code using the results of your first query rather than doing it all in SQL but either way you can dynamically write the SQL containing the specific columns you need. — Dazz Knowles, Aug 18 '21 at 10:21
Are you sure it's SQL Server, not MySQL? Becausa `information_schema` is MySQL related. — Marcus Vinicius Pompeu, Aug 18 '21 at 11:46
@MarcusViniciusPompeu `information_Schema` is standard SQL and supported in SQL Server. Also, the usage of square brackets (`[` /`]`) suggests SQL Server. In MySql you would use `\`` instead. — Zohar Peled, Aug 18 '21 at 11:48
Seems like your data model is broken. Instead of using a different column for each date, use a different row for each date. — Zohar Peled, Aug 18 '21 at 11:51
@ZoharPeled The original Data contains columns with dates. The aim here is to unpivot in order to obtain dates in rows. However, in order to unpivot i am required to write out each date for 2020, from 2020-01-01 to 2020-12-31. Hence the question as to how can i avoid writing each column name out — Newbie, Aug 18 '21 at 13:27

sacse · Accepted Answer · 2021-08-18T16:50:25.440

1

You can use dynamic unpivot with the columns which you already selected. Following sample query might help you.

DECLARE 
  @sql  NVARCHAR(MAX) = N'',
  @cols NVARCHAR(MAX) = N'';

SELECT @cols += ', ' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('yourtable')
  AND name LIKE '2020%';

SET @cols =  STUFF(@cols, 1, 1, '')

SELECT @sql = N'SELECT Other_columns, date_columns, val
  FROM 
  (
    SELECT Other_columns, '  + @cols + '
    FROM yourtable
  ) t
  UNPIVOT
  (
    val FOR date_columns IN (' + @cols + ')
  ) up;';
PRINT @sql;
-- EXEC sp_executesql @sql;

Please find the final db<>fiddle here.

edited Aug 18 '21 at 16:50

answered Aug 18 '21 at 11:52

sacse

3,634
2
15
24

When i try run this code i get the following error. Any idea why this is the case?? - Incorrect syntax near ' + STUFF(@cols, 1, 1, ') + '. – Newbie Aug 18 '21 at 13:25
@Newbie Sorry for that. I have corrected some dynamic SQL errors, please see if the printed query is coming fine before executing. – sacse Aug 18 '21 at 13:52
Apologies i still can't seem to get this right - would you mind creating an example in an online SQL sever platform – Newbie Aug 18 '21 at 14:30
@Newbie I again updated the answer, dynamic SQL sometimes gives issues. Please see the sample [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=2d57c1e92639437444b2bb228cc33b5e). Sorry about the issues and hopefully this time it will work fine. – sacse Aug 18 '21 at 15:16
This doesn't quite give me the result i want unfortunately - ideally i'd like the dates in 1 column and the values in another column. [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7ebb0c839c5079bc229b71bf2522c100) – Newbie Aug 18 '21 at 15:45
@Newbie Got it, please check [here](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8f229de14d3cbaade81b0c94a252af44). I have also updated the answer :). – sacse Aug 18 '21 at 16:07
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/236150/discussion-between-newbie-and-sacse). – Newbie Aug 18 '21 at 16:33

Marcus Vinicius Pompeu · Answer 2 · 2021-08-18T13:37:57.120

Even though I and Zohar do not understand why do exists a table with a column for each day...

For MySQL

Please, consider the solution in this fiddle: https://www.db-fiddle.com/f/7nxjo6hGRsX3Hn8GNqXBuC/0

create table dummy(
  id int,
  --
  relevant_data_01 varchar(255),
  relevant_data_02 varchar(255),
  --
  `20200101` int,
  `20200102` int,
  `20200103` int
);

insert into dummy values(1, 'a', 'b', 1, 2, 3);

select
  concat(
    '
      select *
      from (
    ',
      group_concat(
        concat('
          select
            id, relevant_data_01, relevant_data_02,
            ''', column_name, ''' as date_key,
            `', column_name, '`   as date_value
          from dummy')
        separator
          '
          union all
          '
      ),
    '
      ) d'
  )
into @sql
from INFORMATION_SCHEMA.columns
where
    table_name = 'dummy'
and column_name like '2020%';

PREPARE stmt FROM @sql;
EXECUTE stmt;

For SQL Server

Editing the solution, for SQL Server I provided this other fiddle: http://sqlfiddle.com/#!18/f7874c/14

create table dummy(
  id int,
  --
  relevant_data_01 varchar(255),
  relevant_data_02 varchar(255),
  --
  [20200101] int,
  [20200102] int,
  [20200103] int
)

insert into dummy values(1, 'a', 'b', 1, 2, 3)

declare @sql nvarchar(max) = ''

select
  @sql =
    @sql +
    case
      when @sql = '' then ''
                     else char(13) + char(10) + 'union all' + char(13) + char(10)
    end + '
      select
        id, relevant_data_01, relevant_data_02,
        ''' + col.name + ''' as date_key,
        [' + col.name + ']   as date_value
      from dummy
    '
from
  sys.columns col
where
    col.object_id = OBJECT_ID('dummy')
and col.name like '2020%'
  
execute sp_executesql @sql

I have dates as columns as this is how the data is initially presented. My plan was to unpivot the table (in order to have dates as rows) however, i would be required to name each column which is where i am trying to find a shortcut. I believe the above solution is in MySQL whilst i am using SQLServer and therefore i am receiving multiple errors — Newbie, Aug 18 '21 at 13:08
@Newbie, I edited the solution with a SQL Server version. Good findings. — Marcus Vinicius Pompeu, Aug 18 '21 at 13:38
is there a way of putting this into an assigned Table - i keep getting the following error "A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable." — Newbie, Aug 18 '21 at 14:53
@Newbie, could you edit your question and post the script yielding this error, "A SELECT INTO ... cannot contain a SELECT... to a variable"? — Marcus Vinicius Pompeu, Aug 21 '21 at 00:08

Dynamic SQL Column Selection

2 Answers2

For MySQL

For SQL Server