| |

VerySource

 Forgot password?
 Register
Search
View: 10|Reply: 7

Questions about the summary display

[Copy link]

1

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

Post time: 2020-3-10 18:00:01
| Show all posts |Read mode
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!
Reply

Use magic Report

0

Threads

6

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-6-2 12:00:01
| Show all posts
Reply

Use magic Report

0

Threads

6

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-6-2 12:15:02
| Show all posts
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
Reply

Use magic Report

0

Threads

106

Posts

62

Credits

Newbie

Rank: 1

Credits
62

 China

Post time: 2020-6-2 12:45:01
| Show all posts
Row to column
Reply

Use magic Report

0

Threads

6

Posts

4

Credits

Newbie

Rank: 1

Credits
4

 China

Post time: 2020-6-2 18:15:01
| Show all posts
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)
*/
Reply

Use magic Report

0

Threads

91

Posts

44

Credits

Newbie

Rank: 1

Credits
44

 China

Post time: 2020-6-2 23:15:02
| Show all posts
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
Reply

Use magic Report

1

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

 Author| Post time: 2020-6-4 18:00:01
| Show all posts
If you create a table with Crate Table, then Inster INTO is inserted in four times. How to write it?
Reply

Use magic Report

0

Threads

2

Posts

3

Credits

Newbie

Rank: 1

Credits
3

 China

Post time: 2020-6-8 20:00:01
| Show all posts
You can use Pivot conversion function!
Reply

Use magic Report

You have to log in before you can reply Login | Register

Points Rules

Contact us|Archive|Mobile|CopyRight © 2008-2020|verysource.com ( 京ICP备17048824号-1 )

Quick Reply To Top Return to the list