SQL语句请教

[sunshineos]

商品交易流水表，一共5台机器销售商品

ID 商品名称 机器ID
1 商品1 A2
2 商品2 A1
3 商品1 A1
4 商品3 A3
5 商品2 A2

想实现报表如下，按商品成交最高数量从高到低排序

商品名称 销售数量 A1 A2 A3 A4 A5
商品2 20 2 5 3 9 1
商品1 19 1 4 2 6 6


只显示商品数量统计我会，

select 商品名称,count(商品名称) as total
from 商品交易流水表
group by 商品名称
order by total desc

但是后面再加上设备分组统计数量该怎么写呢，请教各位大神

[中国风]

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[商品名称] nvarchar(23),[机器ID] nvarchar(22))
Insert #T
select 1,N'商品1',N'A2' union all
select 2,N'商品2',N'A1' union all
select 3,N'商品1',N'A1' union all
select 4,N'商品3',N'A3' union all
select 5,N'商品2',N'A2'
Go
SELECT  [商品名称] ,
        COUNT(*) AS 数量 ,
        SUM(CASE WHEN [机器ID] = 'A1' THEN 1
                 ELSE 0
            END) AS A1 ,
        SUM(CASE WHEN [机器ID] = 'A2' THEN 1
                 ELSE 0
            END) AS A2 ,
        SUM(CASE WHEN [机器ID] = 'A3' THEN 1
                 ELSE 0
            END) AS A3 ,
        SUM(CASE WHEN [机器ID] = 'A4' THEN 1
                 ELSE 0
            END) AS A4 ,
        SUM(CASE WHEN [机器ID] = 'A5' THEN 1
                 ELSE 0
            END) AS A5
FROM    #T
GROUP BY [商品名称]
ORDER BY 数量 DESC;

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([ID] int,[商品名称] nvarchar(23),[机器ID] nvarchar(22))
Insert #T
select 1,N'商品1',N'A2' union all
select 2,N'商品2',N'A1' union all
select 3,N'商品1',N'A1' union all
select 4,N'商品3',N'A3' union all
select 5,N'商品2',N'A2'
Go
SELECT  [商品名称] ,
        COUNT(*) AS 数量 ,
        SUM(CASE WHEN [机器ID] = 'A1' THEN 1
                 ELSE 0
            END) AS A1 ,
        SUM(CASE WHEN [机器ID] = 'A2' THEN 1
                 ELSE 0
            END) AS A2 ,
        SUM(CASE WHEN [机器ID] = 'A3' THEN 1
                 ELSE 0
            END) AS A3 ,
        SUM(CASE WHEN [机器ID] = 'A4' THEN 1
                 ELSE 0
            END) AS A4 ,
        SUM(CASE WHEN [机器ID] = 'A5' THEN 1
                 ELSE 0
            END) AS A5
FROM    #T
GROUP BY [商品名称]
ORDER BY 数量 DESC;

/*
商品名称	数量	A1	A2	A3	A4	A5
商品1	2	1	1	0	0	0
商品2	2	1	1	0	0	0
商品3	1	0	0	1	0	0
*/