如何从同一表的同一列中检索2个值 [英] How to retrieve 2 values from same column in same table

问题描述

朋友，

         Table name - rskrplsetting
----------------------------------------------------------------
Pfid | module | Type | rpltype | ltyp  | lvalue | points
----------------------------------------------------------------
pf069|Position|Export|Risk     |Percent|   2    |0.5031000000000
pf069|Cover   |Export|Risk     |Percent|   2    |0.5031000000000
pf069|EEFC    |Export|Risk     |Percent|   2    |0.5031000000000
pf069|Loan    |Loan  |Profit   |Percent|   4    |1.0063000000000
pf069|Swap    |Loan  |Profit   |Percent|   4    |1.0063000000000

我必须从表中检索值才能在水晶报表中显示.
例如:

I have to retrieve values from table to display in crystal report.
For example:

Pfid | module | Type | RL Type| Percent  | Points | PL Type | Percent | points

ltyp为"RL类型"，lvalue为百分比"，rpltyp为风险的点
ltyp为"PL类型"，lvalue为百分比"，rpltyp为利润的点

在一个查询中，我必须执行此操作.
如果有人知道这样做，请帮助我.

在此先感谢

ltyp as ''RL Type'',lvalue as ''Percent'',Points where rpltyp is Risk
ltyp as ''PL Type'',lvalue as ''Percent'',Points where rpltyp is Profit

In one query I have to do this.
If anybody know to do this please help me.

Thanks in advance

推荐答案

您可以使用以下查询

You can use the following query

--Create a Temp table for Risk row values
SELECT Pfid, module, Type, ltyp as [RL Type], lvalue as RPercent, points as RPoints
INTO RT
FROM rskrplsetting
WHERE rpltyp = 'Risk'
--Create a Temp table for Profit row values
SELECT Pfid, module, Type, ltyp as [PL Type], lvalue as PPercent, points as PPoints
INTO PT
FROM rskrplsetting
WHERE rpltyp = 'Profit'
--Combine the rows from Temp tables
SELECT ISNULL(RT.pfid,PT.pfid) as pfid, ISNULL(RT.module,PT.module) as module, ISNULL(RT.Type,PT.Type) as Type,
RT.[RL Type], RT.RPercent, RT.RPoints, PT.[PL Type], PT.PPercent, PT.PPoints
FROM RT FuLL OUTER JOIN PT ON RT.pfid = PT.pfid
DROP TABLE RT, PT

我使用了这种编码.可以了.

Dim qry As String ="
qry =选择c.currency作为" Currency1，s.type作为" type1，s.module作为" module1，s.ltyp作为" RL Type，s.lvalue作为'来自rskrplsetting s s.ccy1 = c.curid内连接comportfoliomaster d的rskrplsetting s内部连接并发列表c上的'percent''，s.points，d.pfname s.pfid =''"+ ddlpf.SelectedValue +"''"
qry&=;选择ltyp作为" PL类型，lvalue作为" Percent1，从rskrplsetting中指向" points1，其中rpltyp =" Profit和pfid ="" + ddlpf.SelectedValue +''"''&getquery(qrytype.PolicySetting)
temds = da.retds(qry，"rskrplsetting"，conn)
带有temds.Tables
.Item(0).TableName ="DataTable1"
.Item(1).TableName ="rskrplsetting"
结尾为

Hi,

I used this coding. It''s working.

Dim qry As String = ""
qry = "select c.currency as ''Currency1'', s.type as ''type1'',s.module as ''module1'',s.ltyp as ''RL Type'',s.lvalue as ''Percent'',s.points,d.pfname from rskrplsetting s inner join comcurrencylist c on s.ccy1=c.curid inner join comportfoliomaster d on s.pfid=d.pfid and s.rpltyp=''Risk'' and s.pfid=''" + ddlpf.SelectedValue + "''"
qry &= "; select ltyp as ''PL Type'',lvalue as ''Percent1'', points as ''points1'' from rskrplsetting where rpltyp=''Profit'' and pfid=''" + ddlpf.SelectedValue + "''" ''& getquery(qrytype.PolicySetting)
temds = da.retds(qry, "rskrplsetting", conn)
With temds.Tables
.Item(0).TableName = "DataTable1"
.Item(1).TableName = "rskrplsetting"
End With

这篇关于如何从同一表的同一列中检索2个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！