Brothers help ah, have this demand, according to the date line into columns in the SQL, for a few da-CodePudding

Quotation date this column is dynamic, the same date, conversion to produce only one column, the date, the more the more columns, of course, usually work under normal circumstances, a result table saw column will certainly not infinitely many columns,

CodePudding user response:

 with t as (
Select "c3" as the name, the convert (date, '2019-10-4') as dt, 0.78 as price 
Union all select 'c2', '2019-10-4', 0.91 
Union all select 'b2', '2019-10-20', 1.08 
Union all select 'w3', '2019-10-20', 1.07 
Union all select 'c3', '2019-11-4', 0.81 
Union all select 'c2', '2019-11-4', 0.95 
Union all select 'b2', '2019-11-20', 1.1 
Union all select 'w3', '2019-11-20', 1.15 
) 
Select * from t 
The pivot (Max (price) for dt in ([2019-10-4], [2019-10-20], [2019-11-4], [2019-11-20])) p

You this is the simplest line column, to baidu pivot, as for the date not sure, their dynamic generate SQL commands can be exec after, the recent problems in turn a large line column,,,

CodePudding user response:

Quote: refer to 1st floor illiterate old gu response:

 with t as (
Select "c3" as the name, the convert (date, '2019-10-4') as dt, 0.78 as price 
Union all select 'c2', '2019-10-4', 0.91 
Union all select 'b2', '2019-10-20', 1.08 
Union all select 'w3', '2019-10-20', 1.07 
Union all select 'c3', '2019-11-4', 0.81 
Union all select 'c2', '2019-11-4', 0.95 
Union all select 'b2', '2019-11-20', 1.1 
Union all select 'w3', '2019-11-20', 1.15 
) 
Select * from t 
The pivot (Max (price) for dt in ([2019-10-4], [2019-10-20], [2019-11-4], [2019-11-20])) p 
Thank you for your answer, ask again, how to put in here the subquery, example:
Select * from t
Pivot (Max (price) for dt in (I think in a subquery or stored procedure, but it is always wrong)) p
CodePudding user response:
The pivot and unpivot is behind in the column name, sub queries with stored procedures and functions of the

As a select statement from behind can't like table variables of

The pivot in the back if is dynamic, yourself anew tectonic dynamic SQL, then the exec
CodePudding user response:
Generate dynamic column has been successful, but each time the query, why can't DataGridView refresh to regenerate the column, using dGv. Columns. The Clear () no, after using the direct empty DataGridView Columns,
CodePudding user response:
If it is SQL Server, it has a pivot, to baidu once,
A more general perspective method is to use case expression, such as:
The select products,
The sum (case when quotation date='2019-10-4' then quotation date else 0 end) as' 2019-10-4 ',
.//your other columns
Group by product