求一 SQL语句　，行转列

eminena 2012-04-13 09:27:38
查询语句：

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

结果：
诊断 费用类别 费用金额
白内障 1 30.00
白内障 1 75.00
白内障 1 90.00
白内障 1 75.00
白内障 1 75.00
白内障 1 75.00
白内障 1 90.00
白内障 1 75.00
白内障 1 75.00
白内障 1 90.00
白内障 1 105.00
白内障 2 12.00
白内障 2 12.00
白内障 2 10.00
白内障 2 10.00
白内障 2 10.00
白内障 2 8.00
白内障 2 8.00
白内障 2 10.00
白内障 2 10.00
白内障 2 10.00
白内障 2 14.00
白内障 3 36.00
白内障 3 122.00
白内障 3 120.00
白内障 3 120.00
白内障 3 38.00
白内障 3 35.00
白内障 3 35.00
白内障 3 37.00
白内障 3 35.00
白内障 3 121.00
白内障 3 120.00
白内障 4 105.50
…………
膀胱恶性肿瘤 1 30.00
膀胱恶性肿瘤 1 150.00
膀胱恶性肿瘤 1 330.00
膀胱恶性肿瘤 1 60.00
膀胱恶性肿瘤 1 120.00
膀胱恶性肿瘤 2 16.00
膀胱恶性肿瘤 2 16.00
膀胱恶性肿瘤 2 28.00
膀胱恶性肿瘤 2 18.00
膀胱恶性肿瘤 2 4.00
膀胱恶性肿瘤 3 25.00
膀胱恶性肿瘤 3 334.00
膀胱恶性肿瘤 4 152.00
膀胱恶性肿瘤 4 49.00
膀胱恶性肿瘤 4 29.00
膀胱恶性肿瘤 4 29.00
膀胱恶性肿瘤 4 313.50
膀胱恶性肿瘤 5 277.00

要求实现如下：

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

……

...全文
267 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
开着拖拉机泡妞 2012-04-13
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 的回复:]

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 ……
[/Quote]

都不给我留一个写法，呜呜
99归一 2012-04-13
  • 打赏
  • 举报
 回复 


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 '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 90.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 90.00 union all

select '白内障', 1, 105.00 union all

select '白内障', 2, 12.00 union all

select '白内障', 2, 12.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 8.00 union all

select '白内障', 2, 8.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 14.00 union all

select '白内障', 3, 36.00 union all

select '白内障', 3, 122.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 3, 38.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 37.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 121.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 4, 105.50 union all

select '膀胱恶性肿瘤', 1, 30.00 union all

select '膀胱恶性肿瘤', 1, 150.00 union all

select '膀胱恶性肿瘤', 1, 330.00 union all

select '膀胱恶性肿瘤', 1, 60.00 union all

select '膀胱恶性肿瘤', 1, 120.00 union all

select '膀胱恶性肿瘤', 2, 16.00 union all

select '膀胱恶性肿瘤', 2, 16.00 union all

select '膀胱恶性肿瘤', 2, 28.00 union all

select '膀胱恶性肿瘤', 2, 18.00 union all

select '膀胱恶性肿瘤', 2, 4.00 union all

select '膀胱恶性肿瘤', 3, 25.00 union all

select '膀胱恶性肿瘤', 3, 334.00 union all

select '膀胱恶性肿瘤', 4, 152.00 union all

select '膀胱恶性肿瘤', 4, 49.00 union all

select '膀胱恶性肿瘤', 4, 29.00 union all

select '膀胱恶性肿瘤', 4, 29.00 union all

select '膀胱恶性肿瘤', 4, 313.50 union all

select '膀胱恶性肿瘤', 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 emi

group 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 a

set @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 行受影响)
唐诗三百首 2012-04-13
  • 打赏
  • 举报
 回复 


with 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 emi

pivot(sum(费用金额) for 费用类别 in([1],[2],[3],[4],[5])) t
Miracle_Sky 2012-04-13
  • 打赏
  • 举报
 回复
不知道对不对啊，只是能出来样式啊。



if exists(select * from sysobjects where [name]='tb')

drop table tb

create table [tb]([ZD] varchar(20),[LB] varchar(4),[HZJE] money)

go

insert tb

select 

'白内障','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 tb

group 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_5

from 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
唐诗三百首 2012-04-13
  • 打赏
  • 举报
 回复 


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 '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 90.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 90.00 union all

select '白内障', 1, 105.00 union all

select '白内障', 2, 12.00 union all

select '白内障', 2, 12.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 8.00 union all

select '白内障', 2, 8.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 14.00 union all

select '白内障', 3, 36.00 union all

select '白内障', 3, 122.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 3, 38.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 37.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 121.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 4, 105.50 union all

select '膀胱恶性肿瘤', 1, 30.00 union all

select '膀胱恶性肿瘤', 1, 150.00 union all

select '膀胱恶性肿瘤', 1, 330.00 union all

select '膀胱恶性肿瘤', 1, 60.00 union all

select '膀胱恶性肿瘤', 1, 120.00 union all

select '膀胱恶性肿瘤', 2, 16.00 union all

select '膀胱恶性肿瘤', 2, 16.00 union all

select '膀胱恶性肿瘤', 2, 28.00 union all

select '膀胱恶性肿瘤', 2, 18.00 union all

select '膀胱恶性肿瘤', 2, 4.00 union all

select '膀胱恶性肿瘤', 3, 25.00 union all

select '膀胱恶性肿瘤', 3, 334.00 union all

select '膀胱恶性肿瘤', 4, 152.00 union all

select '膀胱恶性肿瘤', 4, 49.00 union all

select '膀胱恶性肿瘤', 4, 29.00 union all

select '膀胱恶性肿瘤', 4, 29.00 union all

select '膀胱恶性肿瘤', 4, 313.50 union all

select '膀胱恶性肿瘤', 5, 277.00





select 诊断,

isnull([1],0) '类别1金额合计',

isnull([2],0) '类别2金额合计',

isnull([3],0) '类别3金额合计',

isnull([4],0) '类别4金额合计',

isnull([5],0) '类别5金额合计'

from emi

pivot(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)
cherchill 2012-04-13
  • 打赏
  • 举报
 回复
没发现什么简单高效的方法。。同求
AcHerat 元老 2012-04-13
  • 打赏
  • 举报
 回复
又见行转列。。。
楠_ 2012-04-13
  • 打赏
  • 举报
 回复 


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 '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 90.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 75.00 union all

select '白内障', 1, 90.00 union all

select '白内障', 1, 105.00 union all

select '白内障', 2, 12.00 union all

select '白内障', 2, 12.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 8.00 union all

select '白内障', 2, 8.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 10.00 union all

select '白内障', 2, 14.00 union all

select '白内障', 3, 36.00 union all

select '白内障', 3, 122.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 3, 38.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 37.00 union all

select '白内障', 3, 35.00 union all

select '白内障', 3, 121.00 union all

select '白内障', 3, 120.00 union all

select '白内障', 4, 105.50 union all

select '膀胱恶性肿瘤', 1, 30.00 union all

select '膀胱恶性肿瘤', 1, 150.00 union all

select '膀胱恶性肿瘤', 1, 330.00 union all

select '膀胱恶性肿瘤', 1, 60.00 union all

select '膀胱恶性肿瘤', 1, 120.00 union all

select '膀胱恶性肿瘤', 2, 16.00 union all

select '膀胱恶性肿瘤', 2, 16.00 union all

select '膀胱恶性肿瘤', 2, 28.00 union all

select '膀胱恶性肿瘤', 2, 18.00 union all

select '膀胱恶性肿瘤', 2, 4.00 union all

select '膀胱恶性肿瘤', 3, 25.00 union all

select '膀胱恶性肿瘤', 3, 334.00 union all

select '膀胱恶性肿瘤', 4, 152.00 union all

select '膀胱恶性肿瘤', 4, 49.00 union all

select '膀胱恶性肿瘤', 4, 29.00 union all

select '膀胱恶性肿瘤', 4, 29.00 union all

select '膀胱恶性肿瘤', 4, 313.50 union all

select '膀胱恶性肿瘤', 5, 277.00







DECLARE @SQL VARCHAR(8000),@I INT,@N INT

SELECT @SQL = '',@N = 1, @I=MAX(费用类别) FROM emi



WHILE @N<=@I

BEGIN

	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	

END



SET @SQL = 'SELECT 诊断 ' + @SQL + ' FROM emi T1 GROUP BY 诊断'

PRINT @SQL

EXEC (@SQL)





drop table emi
liukeya2008 2012-04-13
  • 打赏
  • 举报
 回复
可以参考别人的博客
http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html
给出的较为全面。。
a584666873a 2012-04-13
  • 打赏
  • 举报
 回复
这是别人总结的，供你参考：
行转列
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 sql语句实现行转列的3种方法
SQL sql语句实现行转列的3种方法系列文章目录 文章目录SQL sql语句实现行转列的3种方法系列文章目录前言第一种方式：静态行转列第二种方式：用povit行转列第三种方式：用存储过程行转列 前言 将列值旋转为列名(即行转列)是我们在开发中经常会遇到的一个需要，下面这篇文章主要给大家介绍了关于sql语句实现行转列的3种方法，分别给出了详细的示例代码，需要的朋友可以参考借鉴，下面来一起学习学习吧 一般在做数据统计的时候会用到行转列，假如要统计学生的成绩，数据库里查询出来的会是这样的，但这并不能达到想要的效
sql语句实现行转列的3种方法
前言 一般在做数据统计的时候会用到行转列，假如要统计学生的成绩，数据库里查询出来的会是这样的，但这并不能达到想要的效果，所以要在查询的时候做一下处理。 CREATE TABLE TestTable( [Id] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) NULL, [Subject] [nvarchar](50) NULL...
SQL行转列、列转行
SQL行转列、列转行 这个主题还是比较常见的，行转列主要适用于对数据作聚合统计，如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。 一、整理测试数据 create table wyc_test( id int(32) not null auto_increment, name varchar(80) default null, date date default n...
死磕：SQL行转列汇总（全网最全最详细）
SQL行转列汇总 阅读目录 一. 基础语法: 二. 典型实例 一. 基础语法: PIVOT用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别(在数据库属性->选项->兼容级别改为 90 ) SQL2008 中可以直接使用 完整语法： .
sql如何行转列，列转行
我们都知道在sql查询中，union 是增行，join是增列，但是如果碰到需要行转列，列转行时，一味的使用 join来完成则会变的异常繁杂。如把一天24小时的行转换成列，则需要join24次，既不方便管理也使sql运行效率很慢，能脱裤子就解决的非要跑到高山游到海底 行转列 那么如何行转列呢，假如有一张表T，字段有name,subject,score,数据如下 Name Subject Score 小明 语文 30 小明 数学 22 小明 英语 66 小花 语文 30 小花 数学 33 小花 英语 67 转换
MS-SQL Server

34,576

社区成员

254,587

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章