多个记录转换为单个记录 [英] multiple records converted to single record

问题描述

早上好..

这是我在sql server中的查询

Good Morning..

This is my query in sql server

select mb.ID,c.Membershipnumber as MemNum,c.Name, mi.ID as ItemId
mi.Name as ItemName ,mb.Amount from MemberMonthlyBill mb, MemberBill m ,MemberItems mi,Customer c where mb.ItemID=mi.ID and m.ItemID=mi.ID and mb.BillingMonth=m.BillingMonth and m.CustomerID=c.ID and mb.CustomerID=c.ID  and
mb.BillingMonth>='2011-04-1' and mb.BillingMonth<='2011-04-30' order by c.ID

结果是

result is

ID  MemNum  Name                  ItemId  ItemName            Amount
1   A001    MR.A.V.R.K.KUMAR        18    LockerRent          25.00
2   A001    MR.A.V.R.K.KUMAR        19    SubscriptionFees    441.20
3   A001    MR.A.V.R.K.KUMAR        20    ShowerCharges       420.26
6   A002    MR. M. ADISANKAR        19    SubscriptionFees    441.20
12  A003    MR. ASHOK KUMAR GUPTA   19    SubscriptionFees    441.20
11  A003    MR. ASHOK KUMAR GUPTA   18    LockerRent          25.00
16  A004    MR. S.S. ANAND          19    SubscriptionFees    0.00
20  A005    MR. A.S. ANAND          19    SubscriptionFees    441.20
24  A006    MR. T.P. ANANTHAM       19    SubscriptionFees    441.20
29  A007    MR. AZIZ MEHDI          19    SubscriptionFees    441.20
28  A007    MR. AZIZ MEHDI          18    LockerRent          25.00
33  A008    CAPT. AMIT CANUMALLA    19    SubscriptionFees    441.20

这里有一个custer，具有不止一项记录，具体取决于itemid.

但我想要像波纹管那样的结果

here one custer having more than one record depending on itemid.

but i want the result like bellow

MemNum  Name                 LockerRent SubscriptionFees Other    Total
A001    MR.A.V.R.K.KUMAR        25.00    441.20          420.26   886.46
A002    MR. M. ADISANKAR                 441.20                   441.20
A003    MR. ASHOK KUMAR GUPTA   25.00    441.20                   466.20

A001客户拥有3条记录和3条记录.
会员项目表
ID int未检查
名称nvarchar(50)已检查
十进制金额(19，2)已检查
MemberGroupID int已检查
TaxMethodID int已检查
状态nchar(1)未选中

MemberMonthlyBill
ID int未检查
BillingMonth datetime已检查
CustomerID int已检查
ItemID int已检查
十进制金额(19，2)已检查

MemberBill
ID int未检查
BillingMonth datetime已检查
CustomerID int未选中
十进制金额(18，0)已检查
GroupID int已检查
ItemID int已检查
已检查数量int
Upd_by nvarchar(50)已检查
Upd_on datetime已检查
键入nchar(1)已检查

客户
ID int未检查
名称nvarchar(255)未选中
会员编号nvarchar(20)已检查
护照没有nvarchar(255)已检查
国家nvarchar(255)已检查
EmergencyContact nvarchar(255)已检查


任何人都建议使用quarl plz

A001 customer having 3 record with 3 items.
Member Items table
ID int Unchecked
Name nvarchar(50) Checked
Amount decimal(19, 2) Checked
MemberGroupID int Checked
TaxMethodID int Checked
Status nchar(1) Unchecked

MemberMonthlyBill
ID int Unchecked
BillingMonth datetime Checked
CustomerID int Checked
ItemID int Checked
Amount decimal(19, 2) Checked

MemberBill
ID int Unchecked
BillingMonth datetime Checked
CustomerID int Unchecked
Amount decimal(18, 0) Checked
GroupID int Checked
ItemID int Checked
Quantity int Checked
Upd_by nvarchar(50) Checked
Upd_on datetime Checked
Type nchar(1) Checked

customer
ID int Unchecked
Name nvarchar(255) Unchecked
Membershipnumber nvarchar(20) Checked
PassportNo nvarchar(255) Checked
Nation nvarchar(255) Checked
EmergencyContact nvarchar(255) Checked


any one suggest the quary plz

推荐答案

您需要查看创建PIVOT表的方法:MSDN [ ^ ]

You need to look at creating a PIVOT TABLE: MSDN[^]

这篇关于多个记录转换为单个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！