SQL multi table row column transfer and cascade row column transfer example code

Time:2021-12-23

There are two tables a and B

Table a Stores Commodity click logs, and table B stores commodities

It is required to display the list of hits of all commodities on the day, with the total number and sorting by day

Copy codeThe code is as follows:
declare @sql varchar(2000)
Set @ SQL =’select convert (varchar (100), A. [time], 23) as time, count (b.title) as total ‘
select @sql [email protected]+ ‘,sum(case b.title when ”’+Title+”’ then 1 else 0 end) as ‘+'[‘+Title+’]’
from (select distinct title from B) as B
set @[email protected]+’ from A as a left join B as b on a.AId=B.Aid
group by CONVERT(varchar(100), a.[Time], 23) order by CONVERT(varchar(100), a.[Time], 23) desc’
exec(@sql)

Display results

time total Commodity 1 Commodity 2 Commodity 3
2013-11-11 5 1 0 4
2013-11-10 13 2 11 1