我想运态查询各种币别与对应的币别值,并在视图中能直接这个动态表,
1先做了一个自定义函数
CREATE FUNCTION cytotal(@BH VARCHAR(5000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT rdno,'SELECT @SQL = @SQL + '
SUM(CASE cy WHEN ''' + A.cy + ''' THEN total2 ELSE 0 END) AS [' + A.cy + '],'
FROM (SELECT DISTINCT cy FROM m_chargelist WHERE rdno = @BH)A
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ' FROM m_chargelist WHERE rdno = @BH GROUP BY rdno' RETURN RTRIM(@SQL)
END下一步不知道该怎么在视图中调用了请帮一下!
1先做了一个自定义函数
CREATE FUNCTION cytotal(@BH VARCHAR(5000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT rdno,'SELECT @SQL = @SQL + '
SUM(CASE cy WHEN ''' + A.cy + ''' THEN total2 ELSE 0 END) AS [' + A.cy + '],'
FROM (SELECT DISTINCT cy FROM m_chargelist WHERE rdno = @BH)A
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ' FROM m_chargelist WHERE rdno = @BH GROUP BY rdno' RETURN RTRIM(@SQL)
END下一步不知道该怎么在视图中调用了请帮一下!
表名:m_chargelist
字段:
编号 币别 金额
rdno CY TOTAL2例:
001 USD 1000
001 RMB 200
002 HKD 300
002 USD 100
003 JPY 500希望用视图能直接调出动态表的值:
RDNO USD RMB HKD JPY ... ...
001 1000 200
002 100 300
003 500
假设表为test,视图为mytest
create view mytest
as
select * from test.
CREATE FUNCTION cytotal(@BH VARCHAR(5000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT rdno,'SELECT @SQL = @SQL + '
SUM(CASE cy WHEN ''' + A.cy + ''' THEN total2 ELSE 0 END) AS [' + A.cy + '],'
FROM (SELECT DISTINCT cy FROM m_chargelist WHERE rdno = @BH)A
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ' into test FROM m_chargelist WHERE rdno = @BH GROUP BY rdno' RETURN RTRIM(@SQL)
ENDselect * from mytest
请问muyanpeng_mail() ,lei_1700() 如存储过程可以把他算出来,用exec cytotal能算出来后,但算出来的结果不知道怎么去调用。请大家指一下!
该表test对应有个视图mytest
谢谢你!按你的方法运行不了,因为没有TEST表,所以视图不让保存!
能不能说明白一点!一步一步说一下,不好意思,本人有点笨!谢谢!
CREATE TABLE TB(rdno varchar(10), CY varchar(10), Total2 int)
insert into TB
select '001','USD',1000
union all select '001','RMB',200
union all select '002','HKD',300
union all select '002','USD',100
union all select '003','JPY',500--查询
--SQL 2000
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+',['+CY+']=MAX(CASE WHEN CY='''+CY+''' THEN Total2 ELSE 0 END) '
FROM TB
GROUP BY CY
ORDER BY CY DESC
SET @SQL='SELECT rdno,'+STUFF(@SQL,1,1,'')+' FROM TB GROUP BY rdno'
EXEC(@SQL)
--SQL 2005
DECLARE @SQL VARCHAR(8000)
SET @SQL=''
SELECT @SQL=@SQL+','+CY
FROM TB
GROUP BY CY
ORDER BY CY DESC
SET @SQL=STUFF(@SQL,1,1,'')
EXEC('
SELECT * FROM TB
PIVOT
(
MAX(Total2)
FOR CY IN ('+@SQL+')
)AS PT
')
--结果
/*
rdno USD RMB JPY HKD
---------- ----------- ----------- ----------- -----------
001 1000 200 0 0
002 100 0 0 300
003 0 0 500 0(3 行受影响)
*/
/*
rdno USD RMB JPY HKD
---------- ----------- ----------- ----------- -----------
001 1000 200 NULL NULL
002 100 NULL NULL 300
003 NULL NULL 500 NULL(3 行受影响)*/