直接在查询分析器里执行:create view aa
as
select item_code,
MAX(CASE class_code WHEN 'MA' THEN material_code ELSE NULL END) AS 'MA',
MAX(CASE class_code WHEN 'MB' THEN material_code ELSE NULL END) AS 'MB',
MAX(CASE class_code WHEN 'MC' THEN material_code ELSE NULL END) AS 'MC',
MAX(CASE class_code WHEN 'MD' THEN material_code ELSE NULL END) AS 'MD',
MAX(CASE class_code WHEN 'ME' THEN material_code ELSE NULL END) AS 'ME',
MAX(CASE class_code WHEN 'MF' THEN material_code ELSE NULL END) AS 'MF',
MAX(CASE class_code WHEN 'MG' THEN material_code ELSE NULL END) AS 'MG',
MAX(CASE class_code WHEN 'MH' THEN material_code ELSE NULL END) AS 'MH',
MAX(CASE class_code WHEN 'MI' THEN material_code ELSE NULL END) AS 'MI',
MAX(CASE class_code WHEN 'MJ' THEN material_code ELSE NULL END) AS 'MJ',
MAX(CASE class_code WHEN 'MK' THEN material_code ELSE NULL END) AS 'MK',
MAX(CASE class_code WHEN 'ML' THEN material_code ELSE NULL END) AS 'ML',
MAX(CASE class_code WHEN 'MM' THEN material_code ELSE NULL END) AS 'MM',
MAX(CASE class_code WHEN 'MN' THEN material_code ELSE NULL END) AS 'MN'
FROM cl_quata_craft
GROUP BY item_code
as
select item_code,
MAX(CASE class_code WHEN 'MA' THEN material_code ELSE NULL END) AS 'MA',
MAX(CASE class_code WHEN 'MB' THEN material_code ELSE NULL END) AS 'MB',
MAX(CASE class_code WHEN 'MC' THEN material_code ELSE NULL END) AS 'MC',
MAX(CASE class_code WHEN 'MD' THEN material_code ELSE NULL END) AS 'MD',
MAX(CASE class_code WHEN 'ME' THEN material_code ELSE NULL END) AS 'ME',
MAX(CASE class_code WHEN 'MF' THEN material_code ELSE NULL END) AS 'MF',
MAX(CASE class_code WHEN 'MG' THEN material_code ELSE NULL END) AS 'MG',
MAX(CASE class_code WHEN 'MH' THEN material_code ELSE NULL END) AS 'MH',
MAX(CASE class_code WHEN 'MI' THEN material_code ELSE NULL END) AS 'MI',
MAX(CASE class_code WHEN 'MJ' THEN material_code ELSE NULL END) AS 'MJ',
MAX(CASE class_code WHEN 'MK' THEN material_code ELSE NULL END) AS 'MK',
MAX(CASE class_code WHEN 'ML' THEN material_code ELSE NULL END) AS 'ML',
MAX(CASE class_code WHEN 'MM' THEN material_code ELSE NULL END) AS 'MM',
MAX(CASE class_code WHEN 'MN' THEN material_code ELSE NULL END) AS 'MN'
FROM cl_quata_craft
GROUP BY item_code
解决方案 »
- 大开眼界,向专家请教!
- 麻烦帮忙:由于需要请赐教一个sql语句该如何写!
- 为什么我用Varchar型,但是在数据后面有些数据偷偷地给我补个空格呢?
- 请教关于sql语句调用DTS包的问题
- SQL server2005没有原数据库,只有数据库备份,怎么进行符合恢复?
- sql 行转列
- Sql Server 2008 行列转换 不会用case when?
- 关于建库!!!(大家帮忙找一找,散分感谢)
- 关于外键的问题???(急急急!!!!!!)
- 修改数据库字符集的问题(老问题)?
- 我的SQLserver服务器,运行一段时间后会被莫名其妙的停掉,我的机子上装了瑞星,没有木马,请各位大虾帮忙呀!!!!
- 我的问题出在哪?急救!!
as
select item_code,
MAX(CASE class_code WHEN 'MA' THEN material_code ELSE NULL END) AS 'MA',
MAX(CASE class_code WHEN 'MB' THEN material_code ELSE NULL END) AS 'MB',
MAX(CASE class_code WHEN 'MC' THEN material_code ELSE NULL END) AS 'MC',
MAX(CASE class_code WHEN 'MD' THEN material_code ELSE NULL END) AS 'MD',
MAX(CASE class_code WHEN 'ME' THEN material_code ELSE NULL END) AS 'ME',
MAX(CASE class_code WHEN 'MF' THEN material_code ELSE NULL END) AS 'MF',
MAX(CASE class_code WHEN 'MG' THEN material_code ELSE NULL END) AS 'MG',
MAX(CASE class_code WHEN 'MH' THEN material_code ELSE NULL END) AS 'MH',
MAX(CASE class_code WHEN 'MI' THEN material_code ELSE NULL END) AS 'MI',
MAX(CASE class_code WHEN 'MJ' THEN material_code ELSE NULL END) AS 'MJ',
MAX(CASE class_code WHEN 'MK' THEN material_code ELSE NULL END) AS 'MK',
MAX(CASE class_code WHEN 'ML' THEN material_code ELSE NULL END) AS 'ML',
MAX(CASE class_code WHEN 'MM' THEN material_code ELSE NULL END) AS 'MM',
MAX(CASE class_code WHEN 'MN' THEN material_code ELSE NULL END) AS 'MN'
FROM cl_quata_craft
GROUP BY item_code
你试试
create view view1
as
select item_code,
MAX(CASE class_code WHEN 'MA' THEN material_code ELSE NULL END) AS 'MA',
MAX(CASE class_code WHEN 'MB' THEN material_code ELSE NULL END) AS 'MB',
MAX(CASE class_code WHEN 'MC' THEN material_code ELSE NULL END) AS 'MC',
MAX(CASE class_code WHEN 'MD' THEN material_code ELSE NULL END) AS 'MD',
MAX(CASE class_code WHEN 'ME' THEN material_code ELSE NULL END) AS 'ME',
MAX(CASE class_code WHEN 'MF' THEN material_code ELSE NULL END) AS 'MF',
MAX(CASE class_code WHEN 'MG' THEN material_code ELSE NULL END) AS 'MG',
MAX(CASE class_code WHEN 'MH' THEN material_code ELSE NULL END) AS 'MH',
MAX(CASE class_code WHEN 'MI' THEN material_code ELSE NULL END) AS 'MI',
MAX(CASE class_code WHEN 'MJ' THEN material_code ELSE NULL END) AS 'MJ',
MAX(CASE class_code WHEN 'MK' THEN material_code ELSE NULL END) AS 'MK',
MAX(CASE class_code WHEN 'ML' THEN material_code ELSE NULL END) AS 'ML',
MAX(CASE class_code WHEN 'MM' THEN material_code ELSE NULL END) AS 'MM',
MAX(CASE class_code WHEN 'MN' THEN material_code ELSE NULL END) AS 'MN'
FROM cl_quata_craft
GROUP BY item_code