1

I have a table like this:

Acct    MasterAcct 
------- -------------
123     M01  
123     M02  
234     M03  
345     M04  
456     M05  
456     M06  
456     M07

I'd like to break the MasterAcct column into three columns (Master1, Master2, Master3). An account can have up to three master accounts associated with it. Example output from the previous table would have account #456 on one row and then the three master account numbers in Master1, Master2, and Master3. Can anyone help?

Moslem Ben Dhaou
  • 6,897
  • 8
  • 62
  • 93
putty
  • 744
  • 1
  • 6
  • 14

1 Answers1

1

You can use PIVOT in conjunction with ROW_NUMBER() ranking function.

SELECT Acct, [1] as [Master1], [2] as [Master2], [3] as [Master3]
FROM
  (
      SELECT Acct, 
            MasterAcct, 
            ROW_NUMBER() OVER(PARTITION BY acct ORDER BY MasterAcct) as mid
      FROM tbl
   ) as t
PIVOT
(
   MIN(MasterAcct)
   FOR mid IN ([1], [2], [3])
) as p

SQL FIDDLE DEMO

You can achieve the same result without using PIVOT. The following query is easy to understand.

SELECT
    Acct,
    MAX(CASE WHEN mid=1 THEN MasterAcct END) as [Master1],
    MAX(CASE WHEN mid=2 THEN MasterAcct END) as [Master2],
    MAX(CASE WHEN mid=3 THEN MasterAcct END) as [Master3]
FROM
   (
      SELECT Acct, 
            MasterAcct, 
            ROW_NUMBER() OVER(PARTITION BY acct ORDER BY MasterAcct) as mid
      FROM tbl
   ) t
GROUP BY Acct

SQL FIDDLE DEMO

Hamlet Hakobyan
  • 32,965
  • 6
  • 52
  • 68
  • Thanks hamlet. Can we do this without partition and row number ? Also, can you please explain the logic here ? I ask because I only know how to do simple pivots. – Erran Morad Mar 25 '14 at 18:10
  • 1
    Thank you so much. That worked perfectly. I'm very new to SQL, so I would also like to understand the logic behind it. Thanks again – putty Mar 25 '14 at 18:18