What SQL Query or View Will Show "Dynamic Columns"

What SQL query or view will show dynamic columns

You want to pivot each of your name-value pair rows in the MyTable... Try this sql:

DECLARE @Data   TABLE (
    DataID      INT IDENTITY(1,1)   PRIMARY KEY,
    Data        VARCHAR(MAX)
)

DECLARE @Meta   TABLE (
    DataID      INT ,
    MetaName    VARCHAR(MAX),
    MetaData    VARCHAR(MAX)
)

INSERT INTO @Data
SELECT 'Data'

INSERT INTO @Meta
SELECT 1, 'Date', CAST(GetDate() as VARCHAR(20))
UNION
SELECT 1, 'Name', 'Joe Test'

SELECT * FROM @Data

SELECT * FROM @Meta

SELECT 
    D.DataID,
    D.Data,
    MAX(CASE MetaName WHEN 'Date' THEN MetaData ELSE NULL END) as Date,
    MAX(CASE MetaName WHEN 'Name' THEN MetaData ELSE NULL END) as Name
FROM
    @Meta M
JOIN    @Data D     ON M.DataID = D.DataID  
GROUP BY
    D.DataID,
    D.Data

dynamic columns in Query Or view

it is not possible to create a view with a dynamic number of columns. You need to specify all the values of ColumnDataName in order for this to work.

You need to pivot your result, here is an example how you can create your view:

CREATE TABLE 
  xxx(SectionID int, ColumnDescription varchar(10), ColumnDataName varchar(10))

INSERT xxx values(2, 'dgj', 'column1')
INSERT xxx values(2, 'ash', 'column2')
INSERT xxx values(8, 'lkhsdh', 'column2')

go

CREATE VIEW v_xxx as
SELECT SectionId, [column1],[column2],[column3] 
FROM xxx
PIVOT
(min(ColumnDescription)  
FOR ColumnDataName
in([column1],[column2],[column3])  
)AS p

go

SELECT * FROM v_xxx

Result:

SectionId   column1   column2   column3
2           dgj       ash       NULL
8           NULL      lkhsdh    NULL

SQL: Dynamic view with column names based on column values in source table

You can perform this with a PIVOT. When doing the PIVOT you can do it one of two ways, with a Static Pivot that you will code the rows to transform or a Dynamic Pivot which will create the list of columns at run-time:

Static Pivot (See SQL Fiddle for Demo):

select id, [user], [engineer], [manu], [OS]
from 
(
    select t.id
        , t.[user]
        , p.ticketid
        , p.label
        , p.value
    from tickets t
    inner join properties p
        on t.id = p.ticketid
) x
pivot
(
    min(value)
    for label in ([engineer], [manu], [OS])
) p

Or you can use a Dynamic Pivot (See SQL Fiddle for Demo):

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.label) 
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, [user], ' + @cols + ' from 
             (
                 select t.id
                        , t.[user]
                        , p.ticketid
                        , p.label
                        , p.value
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            ) x
            pivot 
            (
                min(value)
                for label in (' + @cols + ')
            ) p '

execute(@query)

Both query will return the same results.

View with dynamic columns In SQL Server

This is not possible with a view because SQL Server queries are always statically typed in the sense that both column count, names and types are statically known at execution time.

You need dynamic SQL for dynamic columns.

Views do not support dynamic SQL. You have to find some other way of returning the data, maybe with one row per logical column.

SQL Server select query dynamic column output

Provided the WEEK numbers and Order numbers are consistent, it is a small matter to maintain the column sequence.

You may notice I used #forecast and #article because I did not know your actual table names.

Example

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ArticleID
              ,D.Description 
              ,B.*
         From  #forecast  A
         Join  #article   D on A.ArticleID=D.ArticleID
         Cross Apply (values (''Week''+left(Week,4),Amount) ) B(Item,Value)
      ) A
 Pivot (max([Value]) 
        For [Item] in (' + Stuff((Select ','+QuoteName('Week'+left(Week,4)) 
                                   From (Select Distinct top 100 [Order],Week From #forecast Order by [Order]  ) A  
                                   For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

Returns

ArticleID   Description Week51  Week52  Week1   Week2   Week3
1           Test        0       150     0       200     0

SQL Query to compare dynamic column headers with another static tables columns

You certainly could use sys.columns to return your static columns from Table2 and compare them to the dynamic columns in Table1 and use UNPIVOT on a select of your first row.

I have found that it was far easier to wrap this all in a T-SQL block and insert to two lists into temp tables before comparing due to data type conflicts (probably be solved by using CAST)

BEGIN
  DECLARE @table1 TABLE (colname VARCHAR(MAX))
  DECLARE @table2 TABLE (colname VARCHAR(MAX))

  INSERT INTO @table1 SELECT COLNAME FROM (SELECT a, b, c FROM TABLE1 WHERE...first row condition) a UNPIVOT (COLNAME FOR COLS IN ([a],[b],[c])) a

  INSERT INTO @table2 SELECT CAST (name  AS NVARCHAR(100)) name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE2')

  SELECT a.colname cols1, b.colname cols2 
    FROM @table2 a
    FULL OUTER JOIN @table1 b ON (a.colname = b.colname)
END

You can easily change the final select to return what you want

Dynamically selecting the column to select from the row itself in SQL

You just need a CASE expression...

SELECT
  id,
  SelectedP,
  CASE SelectedP
    WHEN 'P1' THEN P1
    WHEN 'P2' THEN P2
    WHEN 'P3' THEN P3
    WHEN 'P4' THEN P4
    WHEN 'P5' THEN P5
  END
    AS SelectedPValue
FROM
  yourTable

This will return NULL for anything not mentioned in the CASE expression.

EDIT:

An option with just a little less typing...

SELECT
  id, SelectedP, val  
FROM   
  yourTable AS pvt
UNPIVOT  
(
  val FOR P IN   
  (
    P1,
    P2,
    P3,
    P4,
    P5
  )
)
  AS unpvt
WHERE
  SelectedP = P

NOTE: If the value of SelectedP doesn't exist in the UNPIVOT, then the row will not appear at all (unlike the CASE expression which will return a NULL)

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b693738aac0b594cf37410ee5cb15cf5

EDIT 2:

I don't know if this will perform much worse than the 2nd option, but this preserves the NULL behaviour.

(The preferred option is still to fix your data-structure.)

SELECT
  id, SelectedP, MAX(CASE WHEN SelectedP = P THEN val END) AS val
FROM   
   yourTable AS pvt
UNPIVOT  
(
  val FOR P IN   
  (
    P1,
    P2,
    P3,
    P4,
    P5
  )
)
  AS unpvt
GROUP BY
  id, SelectedP

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f3f64d2fb6e11fd24d1addbe1e50f020

Aggregate dynamic columns in SQL Server

When writing Dynamic Query, you start off with a non-dynamic query. Make sure you gets the result of the query is correct before you convert to dynamic query.

For the result that you required, the query will be

with cte as
(
    select it.Unique_Key, ot.System_Name
    from   data it
           left join data ot on it.Unique_Key = ot.Unique_Key
                            and ot.System_Name <> 'IT'
    where  it.System_Name = 'IT'
)
select  [ITKey]    = count(distinct Unique_Key), 
        [ACCOUNTS] = count(case when System_Name = 'ACCOUNTS' then Unique_Key end) * 100.0 
                   / count(distinct Unique_Key),
        [HR]       = count(case when System_Name = 'HR'       then Unique_Key end) * 100.0 
                   / count(distinct Unique_Key),
        [PAYROLL]  = count(case when System_Name = 'PAYROLL'  then Unique_Key end) * 100.0 
                   / count(distinct Unique_Key)
from   cte;

Once you get the result correct, it is not that difficult to convert to dynamic query. Use string_agg() or for xml path for those repeated rows

declare @sql nvarchar(max);

; with cte as
(
    select distinct System_Name
    from   data
    where  System_Name <> 'IT'
)
select  @sql = string_agg(sql1 + ' / ' + sql2, ',' + char(13))
from    cte
        cross apply
        (
            select sql1 = char(9) + quotename(System_Name) + ' = '
                        + 'count(case when System_Name = ''' + System_Name + ''' then Unique_Key end) * 100.0 ',
                   sql2 = 'count(distinct Unique_Key)'
        ) a

select  @sql = 'with cte as' + char(13)
             + '(' + char(13)
             + '    select it.Unique_Key, ot.System_Name' + char(13)
             + '    from   data it' + char(13)
             + '           left join data ot on it.Unique_Key = ot.Unique_Key' + char(13)
             + '                            and ot.System_Name <> ''IT''' + char(13)
             + '    where  it.System_Name = ''IT''' + char(13)
             + ')' + char(13)
             + 'select [ITKey] = count(distinct Unique_Key), ' + char(13)
             + @sql + char(13)
             + 'from   cte;' + char(13)

print @sql;

exec sp_executesql @sql;

db<>fiddle demo

---

Related Topics