i create dynamic table date wise based on the user selection lets suppose user select date from 15-9 to 19-9 so i create table as below in which i have dates as columns

i create this query as example to reproduce this table

with dates ( [15/09/2018],[16/09/2018],[17/09/2018],[18/09/2018],[19/09/2018]) as 
(  select  '1' ,'0','1','1','1' union all
   select  '1' ,'0','0','1','1' union all
   select  '1' ,'0','1','0','1' union all
   select  '0' ,'0','1','1','1' union all
   select  '0' ,'0','0','1','0'
)
select  [15/09/2018],[16/09/2018],[17/09/2018],[18/09/2018],[19/09/2018]  from dates

now i want to create a temp table in SQL in which i have extra row which has the name day of the column header like 15/09/2018 day name is wed and so on

not sure what to search and how to do it

the above query is just a query so you can create table which i am using (as example) , the dates in column header are dynamic so i can not hard code them , so i need some query which read column header , get day name from it and add it in row under that column

CodePudding user response：

You can create a temp table with the column names as part of your select using select into:

with dates2(...)
select
[15/09/2018],[16/09/2018],[17/09/2018],[18/09/2018],[19/09/2018]
into #MyTempTable
from dates2

CodePudding user response：

Basically, you can create the table, then use information_schema to obtain the column names, which can be used to generate the day names for each column name, then PIVOT that result into a row to be UNION'd into the data.

CREATE TABLE test (
   [15/09/2018]   varchar(12)
 , [16/09/2018]   varchar(12)
 , [17/09/2018]   varchar(12)
 , [18/09/2018]   varchar(12)
 , [19/09/2018]   varchar(12)
);

with dates2 (col1, col2, col3, col4, col5) as 
(
   select  '1' ,'0','1','1','1' union all
   select  '1' ,'0','0','1','1' union all
   select  '1' ,'0','1','0','1' union all
   select  '0' ,'0','1','1','1' union all
   select  '0' ,'0','0','1','0'
)
INSERT INTO test
select * from dates2
;

-- Adjusted to order by `ord` and to use `UNION ALL` to prevent removal of non-unique rows.

WITH cte AS (
         SELECT 1 AS ord, * FROM test
          UNION ALL
         SELECT 0 AS ord,
           [1], [2], [3], [4], [5]
         FROM
         (
           SELECT ORDINAL_POSITION, DATENAME(dw, CONVERT(DATE, COLUMN_NAME, 103)) AS colname
             FROM INFORMATION_SCHEMA.columns
            WHERE table_schema = 'dbo'
              AND table_name   = 'test'
         ) AS SourceTable
         PIVOT
         (
           MAX( colname )
           FOR ORDINAL_POSITION IN ([1], [2], [3], [4], [5])
         ) AS PivotTable
     )
SELECT * FROM cte ORDER BY ord
;

and to create the temp table:

WITH cte AS (
         SELECT 1 AS ord, * FROM test
          UNION ALL
         SELECT 0 AS ord,
           [1], [2], [3], [4], [5]
         FROM
         (
           SELECT ORDINAL_POSITION, DATENAME(dw, CONVERT(DATE, COLUMN_NAME, 103)) AS colname
             FROM INFORMATION_SCHEMA.columns
            WHERE table_schema = 'dbo'
              AND table_name   = 'test'
         ) AS SourceTable
         PIVOT
         (
           MAX( colname )
           FOR ORDINAL_POSITION IN ([1], [2], [3], [4], [5])
         ) AS PivotTable
     )
SELECT * INTO #TmpTable FROM cte
;

SELECT * FROM #TmpTable ORDER BY ord;

The test case: