请问SQL语句应该怎么写?(100分)

SQL纵横查询显示语句的写法；<br>数据表结构如下：<br><br>名称 &nbsp; &nbsp; &nbsp; &nbsp; 石头 &nbsp; &nbsp; &nbsp; 煤 &nbsp; &nbsp; &nbsp;矿<br>大车沪A1 &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp;200 &nbsp; &nbsp; 300<br>大车沪A2 &nbsp; &nbsp; 200 &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; 400<br>大车沪B1 &nbsp; &nbsp; 300 &nbsp; &nbsp; &nbsp; &nbsp;100 &nbsp; &nbsp; 200<br>大车沪B2 &nbsp; &nbsp; 100 &nbsp; &nbsp; &nbsp; &nbsp;200 &nbsp; &nbsp; 300<br>大车沪C1 &nbsp; &nbsp; 200 &nbsp; &nbsp; &nbsp; &nbsp;200 &nbsp; &nbsp; 300<br>大车沪C2 &nbsp; &nbsp; 200 &nbsp; &nbsp; &nbsp; &nbsp;300 &nbsp; &nbsp; 300<br>N多条...<br><br>报表中显示如下：<br><br>---------------------------------<br>&nbsp; &nbsp; &nbsp; &nbsp;大车沪A1 &nbsp; &nbsp; 大车沪A2<br>---------------------------------<br>合计：<br>---------------------------------<br>---------------------------------<br>&nbsp; &nbsp; &nbsp; &nbsp;大车沪B1 &nbsp; &nbsp; 大车沪B2<br>---------------------------------<br>合计：<br>---------------------------------<br>---------------------------------<br>&nbsp; &nbsp; &nbsp; &nbsp;大车沪C1 &nbsp; &nbsp; 大车沪C2<br>---------------------------------<br>合计：<br>---------------------------------<br>请问SQL语句应该怎么写？？？？？？？

给你个例子看一下,应该用的上<br> with dm.qyhuiz do<br>&nbsp; begin<br>&nbsp; sql.Clear;<br>&nbsp; &nbsp; &nbsp;sql.Add( 'select pjrq,qyname,qid, zlpjdw, pjdf,gs,''dj''= &nbsp; '+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp;CASE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp; &nbsp; WHEN pjdf IS NULL THEN ''NULL'' &nbsp;'+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp; &nbsp; &nbsp;WHEN pjdf &gt;=85 THEN ''A'' &nbsp; &nbsp; &nbsp; &nbsp;'+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp; &nbsp; &nbsp;WHEN pjdf &lt;85 and pjdf&gt;=70 THEN ''B'' &nbsp; '+<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN pjdf &lt;70 THEN ''C'' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE ''ERROR'' &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp; &nbsp;END &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' &nbsp; &nbsp;from hz_view &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ' where (qid like '''+'%'+rzedit1.Text+'%'+''' or qyname '+<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'like '''+'%'+rzedit1.Text+'%'+ ''') and pjrq='''+year.Text+''' ') ;<br>&nbsp; &nbsp; &nbsp;open;<br>&nbsp; &nbsp; end;

如果是sql server的话就用case语句，oracle的话的看版本的，这个是典型的列转行，哈哈，俺经常用到，用case语句，挺好的。

select 合计=（石头+ 煤 +矿<br>） from 表名 where 名称=大车沪A1

select &nbsp;石头 ，煤, 矿,sum(石头, 煤, 矿 )as 大车沪A1 from table

请教<br>求一SQLSERVER查询语句：<br>表：<br>姓名 &nbsp; &nbsp; 状态 &nbsp; &nbsp; 金额 &nbsp; 学科<br>张三 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; 数学<br>李四 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; 语文<br>王二 &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp;40 &nbsp; 数学<br>小宋 &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; 语文<br>王五 &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; 语文<br>&nbsp; <br><br><br>查询后：<br>状态0 &nbsp; 状态1 &nbsp; &nbsp;状态0金额合计 &nbsp; &nbsp;状态1金额合计 &nbsp; &nbsp;学科<br>&nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; 语文<br>&nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;40 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;50 &nbsp; &nbsp; &nbsp; &nbsp; 数学<br><br>to：mlc67<br>case语句怎么用？

楼上的和楼主的差不多都是行列转换,建议还是写成Store Procedure吧...

select sum(case when 状态=1 then 1 else 0 end) 状态0,sum(case when 状态=1 then 0 else 1 end) 状态1,<br>sum(case when 状态=1 then 金额 else 0 end) 状态0金额合计,sum(case when 状态=1 then 0 else 金额 end) 状态1金额合计,<br>学科<br>from 表<br>group by 学科