行转列查询方式,需要脚本处理

local m_pGrid = CLuaGridUtil();
m_pGrid:SetGrid(ui:getDialogName(),'id_grid');

function click_id_tb_query()
    local sql = [[
        select distinct field,info from bs_obj_ext_define with(nolock) 
        where table_name = 'bs_material' 
    ]];
    local helpInfo = CSelectHelp();
    if db:select(sql,'',helpInfo) < 0 then
        return ui:tip(db:error());
    end
    sql = [[
        SELECT B.pk_value,
               B.material_code,
               B.material_name,
               B.material_spec,
               %s
          FROM (
            SELECT ext.pk_value,
                   mat.material_code,
                   mat.material_name,
                   mat.material_spec,
                   def.info,
                   ext.[value]
              FROM bs_obj_ext AS ext
              LEFT OUTER JOIN bs_obj_ext_define AS def ON def.table_name = ext.table_name AND def.field = ext.field
              LEFT OUTER JOIN bs_material AS mat ON mat.material_id = ext.pk_value
             WHERE ext.table_name = 'bs_material'
        ) AS A 
        PIVOT(MIN([value]) for info IN (%s)) AS B
        ORDER BY B.pk_value
    ]];
    local sColumns = '';
    for i=0,helpInfo:size()-1 do
        sColumns = sColumns..'['..helpInfo:vs(i,'info')..'],';
    end
    sColumns = cutil:substr(sColumns,0,#sColumns-1);
    sql = string.format(sql,sColumns,sColumns);
    local help = CSelectHelp();
    if db:select(sql,'',help) < 0 then
        return ui:tip(db:error());
    end
    m_pGrid:setHelp(help);
end

click_id_tb_query();

行转列存储过程方式查询

DECLARE @strSql NVARCHAR(1000)      --定义保存最终执行的SQL语句
DECLARE @strColumns NVARCHAR(500)   --定义变量保存要转化为列的数据

--赋值很重要!不赋值执行没结果
SET @strSql = ''
SET @strColumns = ''

--把所有要转化为列的数据保存在字符串中,并且以逗号分隔
SELECT @strColumns = @strColumns + info FROM (SELECT DISTINCT '['+info+'],' AS info FROM bs_obj_ext_define WHERE table_name = 'bs_material') AS T1
--去掉末尾的一个逗号
SET @strColumns=SUBSTRING(@strColumns,1,LEN(@strColumns)-1)
--打印@strColumns
--PRINT(@strColumns)

--使用PIVOT运算符转化
SET @strSql= '
SELECT B.pk_value       AS 物料编号,
       B.material_code  AS 物料编码,
       B.material_name  AS 物料名称,
       B.material_spec  AS 物料规格,
       ' + @strColumns + '
  FROM (
    SELECT ext.pk_value,
           mat.material_code,
           mat.material_name,
           mat.material_spec,
           def.info,
           ext.[value]
      FROM bs_obj_ext AS ext
      LEFT OUTER JOIN bs_obj_ext_define AS def ON def.table_name = ext.table_name AND def.field = ext.field
      LEFT OUTER JOIN bs_material AS mat ON mat.material_id = ext.pk_value
     WHERE ext.table_name = ''bs_material''
) AS A 
PIVOT(MIN([value]) for info IN (' + @strColumns + ')) AS B
ORDER BY B.pk_value'

 --打印最终执行的SQL
 --PRINT(@strSql)
 --执行sql
 EXEC (@strSql)
Copyright © TouchNet 2015 all right reserved,powered by Gitbook最后修订时间: 2022-06-21 09:21:02

results matching ""

    No results matching ""