if a customer can have up to 10 acounts and each account has a balance,
in customer_account table, the same custID repeats and relates to an
accountID and each account has its own balance.
How to display each customer in ONE row and list his account one next to another ?
for example:
custID, acct#1, acct#1Balance, acct#2, acct#2Balance, acct#3, acct#3Balance
how to do write SQl to achive it ?
This is chanllenging to me...
Your advise is greatly appreciated !
Most likely, you will find value in the new PIVOT keyword.
This previous thread may help direct you.
|||just to add what Arnie has said.. PIVIOT is only available in SQL Server 2005.
Madhu
|||Since you have to pivot more than one column, I recommend to use the Case..When (legacy approach).
Here the sample, (this sample is for SQL Server 2005 only – let me know if you SQL Server 2000)
Code Snippet
Create Table #accounts (
[CustomerId] int ,
[AccountNo] int ,
[Balance] money
);
Insert Into #accounts Values('1','272837282','100000');
Insert Into #accounts Values('1','272837298','20000');
Insert Into #accounts Values('1','272837390','1000');
Insert Into #accounts Values('1','272937282','100000');
Insert Into #accounts Values('2','272437282','10000');
Insert Into #accounts Values('3','572837282','83734937');
Insert Into #accounts Values('3','572837285','28292');
;With Grouped
as
(
Select * ,Row_Number() Over(Partition By CustomerId Order BY CustomerId) [Acct#] from #accounts
)
Select
CustomerId
,Max(Case When [Acct#] =1 Then [AccountNo] End) [acct#1]
,Max(Case When [Acct#] =1 Then [Balance] End) [acct#1Balance]
,Max(Case When [Acct#] =2 Then [AccountNo] End) [acct#2]
,Max(Case When [Acct#] =2 Then [Balance] End) [acct#2Balance]
,Max(Case When [Acct#] =3 Then [AccountNo] End) [acct#3]
,Max(Case When [Acct#] =3 Then [Balance] End) [acct#3Balance]
,Max(Case When [Acct#] =4 Then [AccountNo] End) [acct#4]
,Max(Case When [Acct#] =4 Then [Balance] End) [acct#4Balance]
,Max(Case When [Acct#] =5 Then [AccountNo] End) [acct#5]
,Max(Case When [Acct#] =5 Then [Balance] End) [acct#5Balance]
--You can add upto n accounts as per your data
From
Grouped
Group By
CustomerId
