Search code examples
sqlsql-serverdatabaset-sqlpivot

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

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

table1

Expected Output TobeLayout

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

Solution

  • 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