How to Take a Column Value to Make it a Field ?

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