SQL data query method of dynamic transfer line column and summary-CodePudding

SQL data table rows to columns (dynamic), and will be back the number of the corresponding columns,
Need to turn the column data is dynamic, so with static column method of implementation,
Please help to give directions,

CodePudding user response:

- data source
Select N 'A' product as the product name, '2020-11-01' as warehousing date, N 'the first half of 202011, as during the period of storage, as warehouse number 1211
Into # temp
Union all
Select N 'A' product as the product name, '2020-11-02' as warehousing date, N 'the first half of 202011, as during the period of storage, as warehouse number 1011
Union all
Select N 'A' product as the product name, '2020-11-20' as warehousing date, N '202011 and A half months after the as during storage, 1021 as warehousing number
Union all
Select N 'product B as the product name,' 2020-11-01 'as warehousing date, N' the first half of 202011, as during the period of storage, as warehouse number 2011
Union all
Select N 'product B as the product name,' 2020-11-02 'as warehousing date, N' the first half of 202011, as during the period of storage, as warehouse number 101
Union all
Select N 'product B as the product name,' 2020-11-20 'as warehousing date, N' 202011 and a half months after the as during storage, 1011 as warehousing number

- to collect
Select a product name, during storage, warehousing number
Into # temp2
The from # temp

Dynamic splicing during the warehousing
-Declare @ date nvarchar (Max)='

Select @ date +='[' + +' during storage] '
The from # temp
During the group by warehousing
The order by desc during storage

The set @ date=RIGHT (@ date, LEN (@ date) - 1)

- dynamic statement
The exec (N 'select *
From # temp2 pivot (sum (storage quantity) for warehousing period (' + @ date +)) in a ')

Drop table # temp
Drop table # temp2

CodePudding user response:

 
Declare @ s nvarchar (Max) 
Select @ s=isnull (@ s + ', ', ') + quotename (warehouse) during the from the Product GROUP BY storage during the order BY DESC during storage 
Exec (' the select [name], '+ +' @ s from (
Select a Product name, put in storage period, the number [total storage]=sum ([warehouse number]) over (partition by [name], [] during storage) from the Product) a 
The pivot (MAX ([total inventory quantity]) for [the] during storage in (' + @ s +)) b ')