Transposing/pivoting columns..... 1

[unifex]

Ok i'm sure this is a fairly straightforward question but Imagine this scenario:

Cust ID Acct num
1 1
1 2
1 3
2 4
2 5
3 6
4 7

I want to get one row per customer ID and pivot the acct num so that you get first, 2nd, 3rd, acctnum:

Cust ID Acc1 Acc2 Acc3
1 1 2 3
2 4 5
3 6
4 7

Get it?

Well i can't find an easy way of doing it in SQL. I'm primarily a SAS programmer and can do it easy in that but don't want to have to start exporting the data, modifying it and putting it back.

Cheers

 

[tdatgod]

Hi,
Maybe I should make a FAQ out of this question.
someone asked this same question 2 months ago. and it has been asked several times in the ANSI SQL forum as well.

According to the ANSI SQL forum there is no way to do with SQL without building an all encompassing case statement.

Thread220-291724

Maybe with a stored procedure or post processing via AWK.

 

[dnoeth]

If the maximum number of accounts is known, then the fastest way is SQL using ugly looking but very efficient CASEs:

select
cust_id
,max(case when rnk = 1 then acct_num end) as acc1
,max(case when rnk = 2 then acct_num end) as acc2
,max(case when rnk = 3 then acct_num end) as acc3
,max(case when rnk = 4 then acct_num end) as acc4
from
(
select
cust_id
,acct_num
,rank() over (partition by cust_id order by acct_num) as rnk
from tab
) dt

group by
cust_id
;

If you're running R3 the derived table would be:
(select
cust_id
,acct_num
,rank(acct_num asc) as rnk
from tab
group by cust_id
) dt

If the maximum number of accounts is unknown, the only way is a Stored Procedure or doing it with your client.

Dieter

 

[unifex]

Thanks these are helpful however the values of acctnum aren't known they are literally 8 digit account numbers in a random order. so using case will not do the trick.

A suggestion from here was to sort and take the min acctnum for a customer id, then delete that row from the table etc.

I don't know exactly how to do this in standard sql hence my other topic regarding stored procs!

Any other ideas would be good - if not thanks anyway!!

 

[dnoeth]

I didn't talk about the maximum value of an acctnum, i meant the maximum number of accounts for any customer.
If this is known, then my SQL will do the job.

Dieter

 

[unifex]

Ah ok i can see it now - sorry. I kinda read the first bit of code and dismissed it. It might do the trick. Dunno how many unique accounts an individual will have but it's worth a try.
Cheers