27,579
社区成员
发帖
与我相关
我的任务
分享
select * from project
select position, project1, project2, project3
from project
pivot
(sum(value) for project in (project1, project2, project3)) pvt
// 如果您是 SQL Server 2008 版本,请使用以下代码,
// 代码经过测试,且并非网上拽取。
// 这个 SQL 是第一个要求,第二个要求只要将最外层的 SELECT 改成 SELECT * 即可。
SELECT classid,
MAX(CASE Rn
WHEN 1 THEN amount
ELSE 0
END) AS sku1,
MAX(CASE Rn
WHEN 2 THEN amount
ELSE 0
END) AS sku2
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY classid ORDER BY amount DESC) AS Rn,
orderid,
classid,
sku,
amount
FROM dbo.TestTB2
) a
WHERE a.Rn < 3
GROUP BY a.classid
ALTER procedure dbo.test
as
declare @sql varchar(1000)
select @sql = isnull(@sql +',','')+ '['+tjlb+']= max(case when tjlb = '''+tjlb+''' then sjhs else 0 end)'
from
(
select e.name as tjlb
from t_m_zy0100 a
left join t_m_zy0301 b on a.zyid = b.zyid
left join t_m_zy0302 c on b.fyjlh = c.fyjlh
left join t_b_fydm d on c.fyid = d.id
left join t_b_fylbdm e on d.zytjlb = e.code
where a.brysid = -1
group by e.name
) t
exec('select ' + @sql +' from (
select a.xm,a.zyh,e.name as tjlb,sum(c.sjhs) as sjhs
from t_m_zy0100 a
left join t_m_zy0301 b on a.zyid = b.zyid
left join t_m_zy0302 c on b.fyjlh = c.fyjlh
left join t_b_fydm d on c.fyid = d.id
left join t_b_fylbdm e on d.zytjlb = e.code
where a.brysid = -1
group by a.xm,a.zyh,e.name) t1 group by t1.xm,t1.zyh')