SQL Server How to Make a pivot out of one table in T-SQL?

zrfyljdw 于 12个月前发布在其他

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

How can I achieve the second picture using T-Sql language deriving from the first picture which is a database table.

Encircled w/ red will be becoming rows and there values will be put on the corresponding date from the slver_date and that slver_date header which is highlighted w/ yellow will become columns containing the date.

The slvr_date values per item are the same count, so I could say that it is not dynamic.

and also the mapping is as follows:

oh_qty = starting onhand

firm_po = Firm Purchase Order

act_demand = firm_demand

tot_plan_trans_out = Planned Transfer Out

foh_ss = FOH-SS

ss_qrt = Safety Stock Qty

planned_purch = Planned Purchase

Expected Output

Someone told me to use Pivot, but I don't have any experience using it.

sql-server

来源：https://stackoverflow.com/questions/77620967/how-to-make-a-pivot-out-of-one-table-in-t-sql

1条答案

按热度按时间

ufj5ltwl1#

Here's a basic example of what pivot + unpivot combo might look like:

create table #example (id int, date datetime, a int, b int, c int, d int)

insert into #example
values  (1, '20200101', 1,2,3,4)
,   (1, '20200102', 5,6,7,8)
,   (1, '20200103', 9,0,1,2)
,   (2, '20230103', 1,0,1,0)

declare @dates nvarchar(max)
,   @dates_pivot nvarchar(max)
,   @sql nvarchar(max)
select  @dates = STRING_AGG('pv.' + QUOTENAME(CONVERT(NVARCHAR(MAX), date, 126)) + ' AS ' + QUOTENAME(replace(CONVERT(NVARCHAR(MAX), date, 6), ' ', '-')), ',') within group(order by date)
,   @dates_pivot = STRING_AGG(QUOTENAME(CONVERT(NVARCHAR(MAX), date, 126)), ',') within group(order by date)
FROm    (
    SELECT  date
    FROM    #example
    GROUP BY date
    ) d

select @sql = N'
    select id, col AS category,' + @dates + N'
    from #example
    unpivot (value for col in (a,b,c,d)) upv
    pivot (max(value) for date in (' + @dates_pivot + N')) pv
    order by id, col
    '

exec(@sql)

@dates_pivot will contains the unique "pivoted" dates, which goes to the pivot operator

@dates is similar, but also contains the alias for the headers if one wants those

Then we build dynamic sql which first unpivots columns a,b,c,d into value and finally pivots it back.

I couldn't get the colors working, but you should be able to figure out the rest

赞(0）回复(0）举报 12个月前

我来回答

SQL Server How to Make a pivot out of one table in T-SQL?

1条答案

相关问题

热门标签

最新问答