SQL Learning Series-row to column problem dynamic column display

The recent process of doing a project requires a more complex statistical report, involving row-to-column issues.

First look at the format requirements for the report:

The format requires a query by date to dynamically query the column headings, and to display the data by date.

For this requirement, you can do a simple example to illustrate the two-step operation:

(1) The basic data is constructed first;

(2) Perform row-to-column operations on the constructed data.

CREATE TABLE Test1
(
int Test1,
KQRQ datetime,
KQSJ nvarchar (30)
);
Insert into test1 (ZH,KQRQ,KQSJ) VALUES (1, ' 2014-1-2 ', ' 10:21 ')
Insert into test1 (ZH,KQRQ,KQSJ) VALUES (1, ' 2014-1-3 ', ' 11:21 ')
Insert into test1 (ZH,KQRQ,KQSJ) VALUES (1, ' 2014-1-4 ', ' 12:21 ')

Insert into test1 (ZH,KQRQ,KQSJ) VALUES (2, ' 2014-1-2 ', ' 13:21 ')
Insert into test1 (ZH,KQRQ,KQSJ) VALUES (2, ' 2014-1-3 ', ' 14:21 ')
Insert into test1 (ZH,KQRQ,KQSJ) VALUES (2, ' 2014-1-4 ', ' 15:21 ')

--Generate SQL statements dynamically
DECLARE @start_date varchar (10) = ' 2014-01-02 ',
@end_date varchar (10) = ' 2014-01-04 ';

DECLARE @date varchar (10),
@sql varchar (max) = ",
@sql2 varchar (8000);

Set @date = @start_date;

Set @sql2 = ' Select zh '


While @date <= @end_date
Begin

Set @sql2 = @sql2 + ', max (case when CONVERT (varchar), kqrq,23) = "' + @date +
"Then KQSJ else" "End" as V_ ' + REPLACE (right (@date, 5), '-', ')

Set @date = CONVERT (varchar), DATEADD (day,1, @date), 120)
End


Set @sql [email protected] + ' from test1
Group by En '

--Production of dynamic SQL statements
Select @sql

By querying, you can get the results by date as column headings.

This query needs to dynamically display the column headings based on the date, so the date in SQL is dynamically compared as the corresponding column.

For this format, you need to understand the row-to-column knowledge, this time using case when syntax to parse.

In the case of SQL, it is necessary to master the basic knowledge in order to quickly break down the task point of the customer's reporting requirements.

SQL Learning Series-row to column problem dynamic column display