Create columns based on data in existing column, duplicate data exists

Question

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?

possible duplicate of SQL Server PIVOT examples?

Stuart Ainsworth
– Stuart Ainsworth

2014-03-25 17:51:44 +00:00
Commented Mar 25, 2014 at 17:51 — Stuart Ainsworth
– Stuart Ainsworth, Commented Mar 25, 2014 at 17:51
Which version of sql server are you use?

Hamlet Hakobyan
– Hamlet Hakobyan

2014-03-25 18:01:29 +00:00
Commented Mar 25, 2014 at 18:01 — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Mar 25, 2014 at 18:01
I use sql server 2005

putty
– putty

2014-03-25 18:07:10 +00:00
Commented Mar 25, 2014 at 18:07 — putty
– putty, Commented Mar 25, 2014 at 18:07

Hamlet Hakobyan · Accepted Answer · 2014-03-25 18:37:45Z

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

edited Mar 25, 2014 at 18:37

answered Mar 25, 2014 at 18:07

Hamlet Hakobyan

33.5k6 gold badges55 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erran Morad Over a year ago

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.

putty Over a year ago

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

Collectives™ on Stack Overflow

Create columns based on data in existing column, duplicate data exists

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related