Questions about the summary display

00cupillon

The available data is as follows (data breakdown of each quarter):
Quarterly Fitem Quantity
1 A 20
2 B 80
3 A 50
4 B 50

The desired results are as follows (the number of quarters for a single ITEM):

FITEM 1 2 3 4
A 20 50
B 80 50

Thank you!

xx521xx

xx521xx

select ftiem,
       sum(case when quarter = 1 then quantity else null end) as [1],
       sum(case when quarter = 2 then quantity else null end) as [2],
       sum(case when quarter = 3 then quantity else null end) as [3],
       sum(case when quarter = 4 then quantity else null end) as [4]
from tb
group by Fitem

wgfxman

Row to column

xx521xx

if object_id('pubs..tb') is not null
   drop table tb
go

create table tb
(
Quarter int,
Fitem varchar(10),
Quantity int
)

insert into tb(quarter, Fitem, quantity) values(1,'A', 20)
insert into tb(quarter, Fitem, quantity) values(2,'B', 80)
insert into tb(quarter, Fitem, quantity) values(3,'A', 50)
insert into tb(quarter, Fitem, quantity) values(4,'B', 50)

select fitem,
       sum(case when quarter = 1 then quantity else null end) as [1],
       sum(case when quarter = 2 then quantity else null end) as [2],
       sum(case when quarter = 3 then quantity else null end) as [3],
       sum(case when quarter = 4 then quantity else null end) as [4]
from tb
group by Fitem

drop table tb

/*result
fitem 1 2 3 4
---------- ----------- ----------- ----------- ------- ----
A 20 NULL 50 NULL
B NULL 80 NULL 50

(The number of rows affected is 2)
*/

恶魔右指

create table ta(quarter int, Fitem varchar(2), quantity int)
insert ta
select 1,'A', 20
union all select 2,'B', 80
union all select 3,'A', 50
union all select 4,'B', 50

declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+rtrim(quarter)+']=max( case quarter when'+rtrim(quarter)+' then quantity end)'
from ta group by quarter
set @sql='select Fitem'+@sql+' from ta group by Fitem'
--print @sql
exec(@sql)
Fitem 1 2 3 4
----- ----------- ----------- ----------- -----------
A 20 NULL 50 NULL
B NULL 80 NULL 50

00cupillon

If you create a table with Crate Table, then Inster INTO is inserted in four times. How to write it?

lay1307

You can use Pivot conversion function!