求一 SQL语句　，行转列解决方法

2012-05-09
求一 SQL语句　，行转列查询语句：select c.ZD 诊断,a.LB 费用类别,a.HZJE 费用金额from SRCXB_ZYBR a,NHBBD
求一 SQL语句　，行转列
查询语句：

select c.ZD 诊断,a.LB 费用类别,a.HZJE 费用金额 from SRCXB_ZYBR a,NHBBD b ,CYZMS c
where a.ZYH=b.ZYH and a.ZYH=c.ZYH
order by c.ZD,a.LB

结果：
诊断费用类别费用金额
白内障130.00
白内障175.00
白内障190.00
白内障175.00
白内障175.00
白内障175.00
白内障190.00
白内障175.00
白内障175.00
白内障190.00
白内障1105.00
白内障212.00
白内障212.00
白内障210.00
白内障210.00
白内障210.00
白内障28.00
白内障28.00
白内障210.00
白内障210.00
白内障210.00
白内障214.00
白内障336.00
白内障3122.00
白内障3120.00
白内障3120.00
白内障338.00
白内障335.00
白内障335.00
白内障337.00
白内障335.00
白内障3121.00
白内障3120.00
白内障4105.50
…………
膀胱恶性肿瘤130.00
膀胱恶性肿瘤1150.00
膀胱恶性肿瘤1330.00
膀胱恶性肿瘤160.00
膀胱恶性肿瘤1120.00
膀胱恶性肿瘤216.00
膀胱恶性肿瘤216.00
膀胱恶性肿瘤228.00
膀胱恶性肿瘤218.00
膀胱恶性肿瘤24.00
膀胱恶性肿瘤325.00
膀胱恶性肿瘤3334.00
膀胱恶性肿瘤4152.00
膀胱恶性肿瘤449.00
膀胱恶性肿瘤429.00
膀胱恶性肿瘤429.00
膀胱恶性肿瘤4313.50
膀胱恶性肿瘤5277.00

要求实现如下：

诊断类别1金额合计类别2金额合计类别3金额合计
白内障
膀胱恶性肿瘤

……

[解决办法]
SQL code
create table emi(诊断 varchar(16), 费用类别 int, 费用金额 decimal(5,2)) insert into emi select '白内障', 1, 30.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 105.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 14.00 union allselect '白内障', 3, 36.00 union allselect '白内障', 3, 122.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 38.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 37.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 121.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 4, 105.50 union allselect '膀胱恶性肿瘤', 1, 30.00 union allselect '膀胱恶性肿瘤', 1, 150.00 union allselect '膀胱恶性肿瘤', 1, 330.00 union allselect '膀胱恶性肿瘤', 1, 60.00 union allselect '膀胱恶性肿瘤', 1, 120.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 28.00 union allselect '膀胱恶性肿瘤', 2, 18.00 union allselect '膀胱恶性肿瘤', 2, 4.00 union allselect '膀胱恶性肿瘤', 3, 25.00 union allselect '膀胱恶性肿瘤', 3, 334.00 union allselect '膀胱恶性肿瘤', 4, 152.00 union allselect '膀胱恶性肿瘤', 4, 49.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 313.50 union allselect '膀胱恶性肿瘤', 5, 277.00select 诊断,isnull([1],0) '类别1金额合计',isnull([2],0) '类别2金额合计',isnull([3],0) '类别3金额合计',isnull([4],0) '类别4金额合计',isnull([5],0) '类别5金额合计'from emipivot(sum(费用金额) for 费用类别 in([1],[2],[3],[4],[5])) t诊断               类别1金额合计   类别2金额合计    类别3金额合计    类别4金额合计    类别5金额合计---------------- ------------- -------------- -------------- -------------- ---------------白内障              855.00         114.00           819.00        105.50          0.00膀胱恶性肿瘤        690.00          82.00           359.00        572.50          277.00(2 row(s) affected)
[解决办法]
不知道对不对啊，只是能出来样式啊。

SQL codeif exists(select * from sysobjects where [name]='tb')drop table tbcreate table [tb]([ZD] varchar(20),[LB] varchar(4),[HZJE] money)goinsert tbselect '白内障','1','30.00'union select'白内障','1','75.00'union select'白内障','1','90.00'union select'白内障','1','75.00'union select'白内障','1','75.00'union select'白内障','1','75.00'union select'白内障','1','90.00'union select'白内障','1','75.00'union select'白内障','1','75.00'union select'白内障','1','90.00'union select'白内障','1','105.00'union select'白内障','2','12.00'union select'白内障','2','12.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','8.00'union select'白内障','2','8.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','10.00'union select'白内障','2','14.00'union select'白内障','3','36.00'union select'白内障','3','122.00'union select'白内障','3','120.00'union select'白内障','3','120.00'union select'白内障','3','38.00'union select'白内障','3','35.00'union select'白内障','3','35.00'union select'白内障','3','37.00'union select'白内障','3','35.00'union select'白内障','3','121.00'union select'白内障','3','120.00'union select'白内障','4','105.50'union select'膀胱恶性肿瘤','1','30.00'union select'膀胱恶性肿瘤','1','150.00'union select'膀胱恶性肿瘤','1','330.00'union select'膀胱恶性肿瘤','1','60.00'union select'膀胱恶性肿瘤','1','120.00'union select'膀胱恶性肿瘤','1','16.00'union select'膀胱恶性肿瘤','1','16.00'union select'膀胱恶性肿瘤','2','28.00'union select'膀胱恶性肿瘤','2','18.00'union select'膀胱恶性肿瘤','2','4.00'union select'膀胱恶性肿瘤','3','25.00'union select'膀胱恶性肿瘤','3','334.00'union select'膀胱恶性肿瘤','4','152.00'union select'膀胱恶性肿瘤','4','49.00'union select'膀胱恶性肿瘤','4','29.00'union select'膀胱恶性肿瘤','4','29.00'union select'膀胱恶性肿瘤','4','313.50'union select'膀胱恶性肿瘤','5','277.00';--select * from tb;/*诊断 类别1金额合计 类别2金额合计 类别3金额合计   白内障  膀胱恶性肿瘤*/with a as(select zd ,lb,SUM(hzje)hj from tbgroup by ZD,LB),b as(select ZD,case when lb=1 then hj else 0 end lb_1,case when lb=2 then hj else 0 end lb_2,case when lb=3 then hj else 0 end lb_3,case when lb=4 then hj else 0 end lb_4,case when lb=5 then hj else 0 end lb_5from a)select  ZD,SUM(lb_1)类别1金额合计,SUM(lb_2)类别2金额合计,SUM(lb_3)类别3金额合计,SUM(lb_4)类别4金额合计,SUM(lb_5)类别5金额合计from b group by ZD 
 
[解决办法]
SQL codewith emi as(select c.ZD 诊断,a.LB 费用类别,a.HZJE 费用金额  from SRCXB_ZYBR a,NHBBD b ,CYZMS c   where a.ZYH=b.ZYH and a.ZYH=c.ZYH)select 诊断,isnull([1],0) '类别1金额合计',isnull([2],0) '类别2金额合计',isnull([3],0) '类别3金额合计',isnull([4],0) '类别4金额合计',isnull([5],0) '类别5金额合计'from emipivot(sum(费用金额) for 费用类别 in([1],[2],[3],[4],[5])) t
[解决办法]
SQL codecreate table emi(诊断 varchar(16), 费用类别 int, 费用金额 decimal(5,2)) insert into emi select '白内障', 1, 30.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 105.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 14.00 union allselect '白内障', 3, 36.00 union allselect '白内障', 3, 122.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 38.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 37.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 121.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 4, 105.50 union allselect '膀胱恶性肿瘤', 1, 30.00 union allselect '膀胱恶性肿瘤', 1, 150.00 union allselect '膀胱恶性肿瘤', 1, 330.00 union allselect '膀胱恶性肿瘤', 1, 60.00 union allselect '膀胱恶性肿瘤', 1, 120.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 28.00 union allselect '膀胱恶性肿瘤', 2, 18.00 union allselect '膀胱恶性肿瘤', 2, 4.00 union allselect '膀胱恶性肿瘤', 3, 25.00 union allselect '膀胱恶性肿瘤', 3, 334.00 union allselect '膀胱恶性肿瘤', 4, 152.00 union allselect '膀胱恶性肿瘤', 4, 49.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 313.50 union allselect '膀胱恶性肿瘤', 5, 277.00--费用类别固定select     [诊断],    sum(case when [费用类别]=1 then [费用金额] else 0 end) as [费用类别1],    sum(case when [费用类别]=2 then [费用金额] else 0 end)    as [费用类别2],    sum(case when [费用类别]=3 then [费用金额] else 0 end)    as [费用类别3],    sum(case when [费用类别]=4 then [费用金额] else 0 end)    as [费用类别4],    sum(case when [费用类别]=5 then [费用金额] else 0 end)    as [费用类别5]from emigroup by [诊断]--费用类别不固定declare @sql varchar(max)set @sql='select [诊断]'select @sql=@sql+' ,sum(case [费用类别] when '+ convert(varchar(20),费用类别) + ' then 费用金额 else 0 end) as[费用类别'+convert(varchar(20),费用类别)+']'from (select distinct [费用类别] from emi) as aset @sql=@sql + ' from emi group by [诊断]'exec (@sql)--结果：诊断               费用类别1                                   费用类别2                                   费用类别3                                   费用类别4                                   费用类别5---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------白内障              855.00                                  114.00                                  819.00                                  105.50                                  0.00膀胱恶性肿瘤           690.00                                  82.00                                   359.00                                  572.50                                  277.00(2 行受影响)
[解决办法]
探讨

SQL code

create table emi
(诊断 varchar(16), 费用类别 int, 费用金额 decimal(5,2)) 
 
 
insert into emi 
select '白内障', 1, 30.00 union all
select '白内障', 1, 75.00 union all
select '白内障', 1, 90.00 union all
select ……

[解决办法]
这是别人总结的，供你参考：
行转列
CREATE TABLE t_row_col AS
SELECT id, 'c1' cn, c1 cv
 FROM t_col_row
UNION ALL
SELECT id, 'c2' cn, c2 cv
 FROM t_col_row
UNION ALL
SELECT id, 'c3' cn, c3 cv FROM t_col_row;

SELECT * FROM t_row_col ORDER BY 1,2;

1)AGGREGATE FUNCTION
适用范围：8i,9i,10g及以后版本
SELECT id,
      MAX(decode(cn, 'c1', cv, NULL)) AS c1,
      MAX(decode(cn, 'c2', cv, NULL)) AS c2,
      MAX(decode(cn, 'c3', cv, NULL)) AS c3
 FROM t_row_col
GROUP BY id
ORDER BY 1;

MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。

被指定的转置列只能有一列，但固定的列可以有多列，请看下面的例子：

SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;

SELECT mgr,
      deptno,
      MAX(decode(empno, '7788', ename, NULL)) "7788",
      MAX(decode(empno, '7902', ename, NULL)) "7902",
      MAX(decode(empno, '7844', ename, NULL)) "7844",
      MAX(decode(empno, '7521', ename, NULL)) "7521",
      MAX(decode(empno, '7900', ename, NULL)) "7900",
      MAX(decode(empno, '7499', ename, NULL)) "7499",
      MAX(decode(empno, '7654', ename, NULL)) "7654"
 FROM emp
WHERE mgr IN (7566, 7698)
  AND deptno IN (20, 30)
GROUP BY mgr, deptno
ORDER BY 1, 2;

这里转置列为empno，固定列为mgr，deptno。

还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：

ID        CN_1        CV_1        CN_2        CV_2        CN_3        CV_3
1                c1                v11                c2                v21                c3                v31
2                c1                v12                c2                v22                c3               
3                c1                v13                c2                                        c3                v33
4                c1                                        c2                v24                c3                v34
5                c1                v15                c2                                        c3               
6                c1                                        c2                                        c3                v35
7                c1                                        c2                                        c3

这种情况可以用分析函数实现：

SELECT id,
      MAX(decode(rn, 1, cn, NULL)) cn_1,
      MAX(decode(rn, 1, cv, NULL)) cv_1,
      MAX(decode(rn, 2, cn, NULL)) cn_2,
      MAX(decode(rn, 2, cv, NULL)) cv_2,
      MAX(decode(rn, 3, cn, NULL)) cn_3,
      MAX(decode(rn, 3, cv, NULL)) cv_3
 FROM (SELECT id,
              cn,
              cv,
              row_number() over(PARTITION BY id ORDER BY cn, cv) rn
         FROM t_row_col)
GROUP BY ID;

2)PL/SQL
适用范围：8i,9i,10g及以后版本  
这种对于行值不固定的情况可以使用。
下面是我写的一个包，包中
p_rows_column_real用于前述的第一种不限定列的转换；
p_rows_column用于前述的第二种不限定列的转换。

CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS
 TYPE refc IS REF CURSOR;

 PROCEDURE p_print_sql(p_txt VARCHAR2);

 FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
   RETURN VARCHAR2;

 PROCEDURE p_rows_column(p_table      IN VARCHAR2,
                         p_keep_cols  IN VARCHAR2,
                         p_pivot_cols IN VARCHAR2,
                         p_where      IN VARCHAR2 DEFAULT NULL,
                         p_refc       IN OUT refc);

 PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
                              p_keep_cols IN VARCHAR2,
                              p_pivot_col IN VARCHAR2,
                              p_pivot_val IN VARCHAR2, 
 
                              p_where     IN VARCHAR2 DEFAULT NULL,
                              p_refc      IN OUT refc);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS

 PROCEDURE p_print_sql(p_txt VARCHAR2) IS
   v_len INT;
 BEGIN
   v_len := length(p_txt);
   FOR i IN 1 .. v_len / 250 + 1 LOOP
     dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250));
   END LOOP;
 END;

 FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)
   RETURN VARCHAR2 IS
   v_first INT;
   v_last  INT;
 BEGIN
   IF p_seq < 1 THEN
     RETURN NULL;
   END IF;
   IF p_seq = 1 THEN
     IF instr(p_str, p_division, 1, p_seq) = 0 THEN
       RETURN p_str;
     ELSE
       RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);
     END IF;
   ELSE
     v_first := instr(p_str, p_division, 1, p_seq - 1);
     v_last  := instr(p_str, p_division, 1, p_seq);
     IF (v_last = 0) THEN
       IF (v_first > 0) THEN
         RETURN substr(p_str, v_first + 1);
       ELSE
         RETURN NULL;
       END IF;
     ELSE
       RETURN substr(p_str, v_first + 1, v_last - v_first - 1);
     END IF;
   END IF;
 END f_split_str;

 PROCEDURE p_rows_column(p_table      IN VARCHAR2,
                         p_keep_cols  IN VARCHAR2,
                         p_pivot_cols IN VARCHAR2,
                         p_where      IN VARCHAR2 DEFAULT NULL,
                         p_refc       IN OUT refc) IS
   v_sql VARCHAR2(4000);
   TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   v_keep v_keep_ind_by;
    
   TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   v_pivot v_pivot_ind_by;
    
   v_keep_cnt   INT;
   v_pivot_cnt  INT;
   v_max_cols   INT;
   v_partition  VARCHAR2(4000);
   v_partition1 VARCHAR2(4000);
   v_partition2 VARCHAR2(4000);
 BEGIN
   v_keep_cnt  := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
   v_pivot_cnt := length(p_pivot_cols) -
                  length(REPLACE(p_pivot_cols, ',')) + 1;
   FOR i IN 1 .. v_keep_cnt LOOP
     v_keep(i) := f_split_str(p_keep_cols, ',', i);
   END LOOP;
   FOR j IN 1 .. v_pivot_cnt LOOP
     v_pivot(j) := f_split_str(p_pivot_cols, ',', j);
   END LOOP;
   v_sql := 'select max(count(*)) from ' || p_table || ' group by ';
   FOR i IN 1 .. v_keep.LAST LOOP
     v_sql := v_sql || v_keep(i) || ',';
   END LOOP;
   v_sql := rtrim(v_sql, ',');
   EXECUTE IMMEDIATE v_sql
     INTO v_max_cols;
   v_partition := 'select ';
   FOR x IN 1 .. v_keep.COUNT LOOP
     v_partition1 := v_partition1 || v_keep(x) || ',';
   END LOOP;
   FOR y IN 1 .. v_pivot.COUNT LOOP
     v_partition2 := v_partition2 || v_pivot(y) || ',';
   END LOOP;
   v_partition1 := rtrim(v_partition1, ',');
   v_partition2 := rtrim(v_partition2, ',');
   v_partition  := v_partition || v_partition1 || ',' || v_partition2 ||
                   ', row_number() over (partition by ' || v_partition1 ||
                   ' order by ' || v_partition2 || ') rn from ' || p_table;
   v_partition  := rtrim(v_partition, ',');
   v_sql        := 'select ';
   FOR i IN 1 .. v_keep.COUNT LOOP
     v_sql := v_sql || v_keep(i) || ',';
   END LOOP;
   FOR i IN 1 .. v_max_cols LOOP
     FOR j IN 1 .. v_pivot.COUNT LOOP
       v_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) ||
                ',null))' || v_pivot(j) || '_' || i || ',';
     END LOOP;
   END LOOP;
   IF p_where IS NOT NULL THEN
     v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||
              p_where || ') group by ';
   ELSE
     v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || 
 
              ') group by ';
   END IF;
   FOR i IN 1 .. v_keep.COUNT LOOP
     v_sql := v_sql || v_keep(i) || ',';
   END LOOP;
   v_sql := rtrim(v_sql, ',');
   p_print_sql(v_sql);
   OPEN p_refc FOR v_sql;
 EXCEPTION
   WHEN OTHERS THEN
     OPEN p_refc FOR
       SELECT 'x' FROM dual WHERE 0 = 1;
 END;

 PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,
                              p_keep_cols IN VARCHAR2,
                              p_pivot_col IN VARCHAR2,
                              p_pivot_val IN VARCHAR2,
                              p_where     IN VARCHAR2 DEFAULT NULL,
                              p_refc      IN OUT refc) IS
   v_sql VARCHAR2(4000);
   TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   v_keep v_keep_ind_by;
   TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
   v_pivot    v_pivot_ind_by;
   v_keep_cnt INT;
   v_group_by VARCHAR2(2000);
 BEGIN
   v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;
   FOR i IN 1 .. v_keep_cnt LOOP
     v_keep(i) := f_split_str(p_keep_cols, ',', i);
   END LOOP;
   v_sql := 'select ' || 'cast(' || p_pivot_col ||
            ' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||
            ' group by ' || p_pivot_col;
   EXECUTE IMMEDIATE v_sql BULK COLLECT
     INTO v_pivot;
   FOR i IN 1 .. v_keep.COUNT LOOP
     v_group_by := v_group_by || v_keep(i) || ',';
   END LOOP;
   v_group_by := rtrim(v_group_by, ',');
   v_sql      := 'select ' || v_group_by || ',';
    
   FOR x IN 1 .. v_pivot.COUNT LOOP
     v_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) ||
              v_pivot(x) || chr(39) || ',' || p_pivot_val ||
              ',null)) as "' || v_pivot(x) || '",';
   END LOOP;
   v_sql := rtrim(v_sql, ',');
   IF p_where IS NOT NULL THEN
     v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||
              v_group_by;
   ELSE
     v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by;
   END IF;
   p_print_sql(v_sql);
   OPEN p_refc FOR v_sql;
 EXCEPTION
   WHEN OTHERS THEN
     OPEN p_refc FOR
       SELECT 'x' FROM dual WHERE 0 = 1;
 END;

END;

[解决办法]
SQL codecreate table emi(诊断 varchar(16), 费用类别 int, 费用金额 decimal(5,2)) insert into emi select '白内障', 1, 30.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 75.00 union allselect '白内障', 1, 90.00 union allselect '白内障', 1, 105.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 12.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 8.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 10.00 union allselect '白内障', 2, 14.00 union allselect '白内障', 3, 36.00 union allselect '白内障', 3, 122.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 3, 38.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 37.00 union allselect '白内障', 3, 35.00 union allselect '白内障', 3, 121.00 union allselect '白内障', 3, 120.00 union allselect '白内障', 4, 105.50 union allselect '膀胱恶性肿瘤', 1, 30.00 union allselect '膀胱恶性肿瘤', 1, 150.00 union allselect '膀胱恶性肿瘤', 1, 330.00 union allselect '膀胱恶性肿瘤', 1, 60.00 union allselect '膀胱恶性肿瘤', 1, 120.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 16.00 union allselect '膀胱恶性肿瘤', 2, 28.00 union allselect '膀胱恶性肿瘤', 2, 18.00 union allselect '膀胱恶性肿瘤', 2, 4.00 union allselect '膀胱恶性肿瘤', 3, 25.00 union allselect '膀胱恶性肿瘤', 3, 334.00 union allselect '膀胱恶性肿瘤', 4, 152.00 union allselect '膀胱恶性肿瘤', 4, 49.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 29.00 union allselect '膀胱恶性肿瘤', 4, 313.50 union allselect '膀胱恶性肿瘤', 5, 277.00DECLARE @SQL VARCHAR(8000),@I INT,@N INTSELECT @SQL = '',@N = 1, @I=MAX(费用类别) FROM emiWHILE @N<=@IBEGIN    SET @SQL = @SQL + ',' + CAST(@N AS VARCHAR) + ' AS [类别 ' + CAST(@N AS VARCHAR) +'],ISNULL((SELECT SUM(费用金额) FROM emi where 诊断=t1.诊断 and 费用类别=''' +CAST(@N as varchar)+ '''),0) AS [金额合计]'     set @N = @N + 1    ENDSET @SQL = 'SELECT 诊断 ' + @SQL + ' FROM emi T1 GROUP BY 诊断'PRINT @SQLEXEC (@SQL)drop table emi
热点排行
SQL Server

求一 SQL语句 ，行转列解决方法

求一 SQL语句　，行转列解决方法
