SQL Server 2014 - dynamic column name

krcsximq 于 2023-10-15 发布在 SQL Server

关注(0)|答案(1)|浏览(140)

We want to select the column name in first view as the get the data as per 2nd view

--First view to get Data

select 
    Product, _2nd_Future_Month, _3rd_Future_Month, _4th_Future_Month
from 
    Rpt_Planning_PP_Screen

--First view Data

Product                         _2nd_Future_Month               _3rd_Future_Month       _4th_Future_Month
Voren 25mg Tablets          4166                8332               4166
Voren 50mg Tablets          117648              127452             117648
Cardiolite 25mg Tablets     5000                10000              5000

--- 2nd view to get heading

select 
    Product, _2nd_Future_Month, _3rd_Future_Month, _4th_Future_Month
from 
    Rpt_Planning_PP_Screen_Heading

--2nd view Data

Product     _2nd_Future_Month       _3rd_Future_Month       _4th_Future_Month
Product     Nov_2023            Dec_2023            Jan_2024

We need a script to get data like this in SQL Server 2014.

Product                     Nov_2023            Dec_2023          Jan_2024
Voren 25mg Tablets          4166                8332               4166
Voren 50mg Tablets          117648              127452             117648
Cardiolite 25mg Tablets     5000                10000              5000

sql-server

来源：https://stackoverflow.com/questions/77112240/sql-server-2014-dynamic-column-name

1条答案

按热度按时间

gab6jxml1#

Something like this perhaps:

-- Create some testdata
select *
into Rpt_Planning_PP_Screen
from (
    VALUES  (N'Voren 25mg Tablets', 4166, 8332, 4166)
    ,   (N'Voren 50mg Tablets', 117648, 127452, 117648)
    ,   (N'Cardiolite 25mg Tablets', 5000, 10000, 5000)
) t (Product,_2nd_Future_Month,_3rd_Future_Month,_4th_Future_Month)

select *
into Rpt_Planning_PP_Screen_Heading
from
(
    VALUES  (N'Product', N'Nov_2023', N'Dec_2023', N'Jan_2024')
) t (Product,_2nd_Future_Month,_3rd_Future_Month,_4th_Future_Month)

-- The actual code
declare @sql nvarchar(max)

select @sql = concat('select '
    , 'Product as ' , quotename(Product)
    , ', _2nd_Future_Month as ', quotename(_2nd_Future_Month)
    , ', _3rd_Future_Month as ', quotename(_3rd_Future_Month)
    , ', _4th_Future_Month as ', quotename(_4th_Future_Month)
    , ' FROM Rpt_Planning_PP_Screen'
    )
from Rpt_Planning_PP_Screen_Heading

exec(@sql)

You just build your dynamic sql and alias it to the columns from the heading table.

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server 2014 - dynamic column name

1条答案

相关问题

热门标签

最新问答