CODEWITHSUNDEEP
sqlsql-server
user3075618
user3075618

Reputation: 23

Transposing Two Columns in SQL

I'm very new to SQL. I looked forever on this site for an answer to this, but I couldn't. I have data that looks like this:

Code |  Name  
A    |  John  
A    |  Bob  
A    |  Chris  
B    |  James  
B    |  Jen  
B    |  Teddy

I would like it to build a query that would result in this:

Code | Name | Name2 | Name3  
A    | John |  Bob  | Chris  
B    | James|  Jen  | Teddy

Any help would be greatly appreciated.

Upvotes: 2

Views: 74

Answers (1)

mellamokb
mellamokb

Reputation: 56779

The solution is to use PIVOT. However, a PIVOT is intended to be used on 'category' values, meaning you have the same set of categories per group. You don't have anything that is similar between each group, like a 'name index' or anything. So you can't identify the PIVOT columns properly.

The simple solution is to manufacture a category for each name in the group, like Name1, Name2, Name3, etc. that you can PIVOT on. This can be done with a ROW_NUMBER() clause like this:

select Code,
  'Name' + cast(
      row_number() over (partition by Code order by code)
  as varchar(10)) as NameType,
  Name
from table1

Which produces results that look like this:

| CODE | NAMETYPE |  NAME |
|------|----------|-------|
|    A |    Name1 |  John |
|    A |    Name2 |   Bob |
|    A |    Name3 | Chris |
|    B |    Name1 | James |
|    B |    Name2 |   Jen |
|    B |    Name3 | Teddy |

Now you have something shared between groups to PIVOT on - the NAMETYPE column:

select * from ( 
  select Code,
    'Name' + cast(
        row_number() over (partition by Code order by code)
    as varchar(10)) as NameType,
    Name
  from table1
) a
pivot ( max(Name) for [NameType] in ([Name1], [Name2], [Name3]) ) piv

This produces the desired results:

| CODE | NAME1 | NAME2 | NAME3 |
|------|-------|-------|-------|
|    A |  John |   Bob | Chris |
|    B | James |   Jen | Teddy |

Demo: http://www.sqlfiddle.com/#!6/21499/7

Upvotes: 1

Related Questions