CODEWITHSUNDEEP
sqlsql-servert-sql
nowYouSeeMe
nowYouSeeMe

Reputation: 1009

Transpose columns in SQL

I'm trying to take the following data;

|-----------------|-----------------|-----------------|
|        ID       |       Make      |       Model     |
|-----------------|-----------------|-----------------|
|        A1       |       Ford      |       Fiesta    |
|-----------------|-----------------|-----------------|
|        A2       |       Peugeot   |       106       |
|-----------------|-----------------|-----------------|

And transpose it so that it looks like so;

|-----------------|-----------------|-----------------|
|        ID       |     FieldName   |    FieldValue   |
|-----------------|-----------------|-----------------|
|        A1       |       Make      |       Ford      |
|-----------------|-----------------|-----------------|
|        A1       |       Model     |       Fiesta    |
|-----------------|-----------------|-----------------|
|        A2       |       Make      |       Peugeot   |
|-----------------|-----------------|-----------------|
|        A2       |       Model     |       106       |
|-----------------|-----------------|-----------------|

Is this possible within SQL?

Upvotes: 0

Views: 56

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

you can use unpivot as below:

Select * from #temp 
unpivot (fieldvalue for fieldName in([make],[model])) p

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I would suggest doing this using cross apply:

select t.id, v.*
from t cross apply
     (values ('make', t.make),
             ('model', t.model)
     ) v(fieldname, fieldvalue);

SQL Server also offers unpivot and union all which can be used for this purpose. However, lateral joins (the technical name for what apply does) are quite powerful and efficient.

Upvotes: 3

Related Questions