Strives for the great god, SQL data query, SQL query-CodePudding

The original data on the surface of the table data is as follows:
NO Date Time City Name
A10 2020/8/1 8:19:26 Shanghai zhang
A10 2020/8/1 8:06:06 Shanghai zhang
A10 2020/8/1 17:06:06 Shanghai zhang
A20 2020/8/1 7:52:46 Shanghai li si
A20 2020/8/1 17:12:43 Shanghai li si
A10 2020/8/2 10:19:26 Shanghai zhang
A10 2020/8/2 10:06:06 Shanghai zhang
A10 2020/8/2 17:26:06 Shanghai zhang
A20 2020/8/2 7:56:46 Shanghai li si
A20 2020/8/2 17:16:43 Shanghai li si
A10 2020/8/3 8:19:26 Shanghai zhang
A10 2020/8/3 8:06:06 Shanghai zhang
A20 2020/8/3 7:50:46 Shanghai li si
A20 2020/8/3 18:10:43 Shanghai li si
A20 2020/8/3 17:09:43 Shanghai li si

Can you write a SQL query, query the following format:

CodePudding user response:

 
The create table # t (NO varchar (10), [the Date Time] datetime, City varchar (10), the Name varchar (10)) 

Insert into # t 
Select 'A10', '2020/8/1 8:19:26', 'Shanghai', 'zhang' union all 
Select 'A10', '2020/8/1 8:06:06', 'Shanghai', 'zhang' union all 
Select 'A10', '2020/8/1 17:06:06', 'Shanghai', 'zhang' union all 
Select 'A20', '2020/8/1 7:52:46', 'Shanghai', 'bill' union all 
Select 'A20', '2020/8/1 17:12:43', 'Shanghai', 'bill' union all 
Select 'A10', '2020/8/2 10:19:26', 'Shanghai', 'zhang' union all 
Select 'A10', '2020/8/2 10:06:06', 'Shanghai', 'zhang' union all 
Select 'A10', '2020/8/2 17:26:06', 'Shanghai', 'zhang' union all 
Select 'A20', '2020/8/2 7:56:46', 'Shanghai', 'bill' union all 
Select 'A20', '2020/8/2 17:16:43', 'Shanghai', 'bill' union all 
Select 'A10', '2020/8/3 8:19:26', 'Shanghai', 'zhang' union all 
Select 'A10', '2020/8/3 8:06:06', 'Shanghai', 'zhang' union all 
Select 'A20', '2020/8/3 7:50:46', 'Shanghai', 'bill' union all 
Select 'A20', '2020/8/3 18:10:43', 'Shanghai', 'bill' union all 
Select 'A20', '2020/8/3 17:09:43', 'Shanghai', 'bill' 


Declare @ TSQL nvarchar (Max) 

Select @ TSQL=isnull (@ TSQL + N ', ', N ') + N '[' + dt + N'] '
The from (select distinct dt=the convert (nvarchar, [the Date Time], 111) from # t) t 

Select @ TSQL=N 'select NO, City, Name,' + @ TSQL 
+ N 'the from (select a.N O, a.C ity, a.N ame, dt=the convert (varchar, a. [the Date Time], 111), 
Ss=stuff ((select char (10) + the convert (varchar, b. [the Date Time], 108) 
The from # t b 
Where b.N o=a.N o 
And biggest ity=a.C ity 
And b.N ame=a.N ame 
And the convert (varchar, b. [the Date Time], 111)=the convert (varchar, a. [the Date Time], 111) 
For XML path (" ', ')), 1, 1, ' ' ' ') 
The from # t a 
Group by a.N O, a.C ity, a.N ame, convert (varchar, a. [the Date Time], 111)) t 
The pivot (Max (ss) for dt in (' + @ TSQL + N)) p 
 '
The exec (@ TSQL)

CodePudding user response:

Eldest brother, the Date and Time are the two types of field the Date and Time (7), a storage Date, another is the storage Time,

If add the Where conditions Where ah, such as the Where name='* *'

CodePudding user response:

Recommend finereport to do statements

CodePudding user response:

To tell you the truth, your this requirement with tableau or Excel digital pivottables, points minutes ready, in SQL is a bit of trouble, is not recommended