Convert Data from Columns to Rows

Question

I have a table with data like the one below.

ID  Full Name   Apples  Mangoes Grapes
1     Name1      15       19    13
2     Name2      11       16    15
3     Name3      12       18    11
4     Name4      16       20    11
5     Name5      14       19    13
6     Name6      19       15    18

I wanted to convert it like this.

ID  Full Name   Fruits  Values
1   Name1       Apples  15
1   Name1       Mangoes 19
1   Name1       Grapes  13
2   Name2       Apples  11
2   Name2       Mangoes 16
2   Name2       Grapes  15
3   Name3       Apples  12
3   Name3       Mangoes 18
3   Name3       Grapes  11
4   Name4       Apples  16
4   Name4       Mangoes 20
4   Name4       Grapes  11
5   Name5       Apples  14
5   Name5       Mangoes 19
5   Name5       Grapes  13
6   Name6       Apples  19
6   Name6       Mangoes 15
6   Name6       Grapes  18

Is there any possibilities that it can be done in Microsoft SQL?

Another simple approach if the columns are known: http://sqlfiddle.com/#!3/7fdda/2/0 — Tim Schmelter, Jun 25 '15 at 13:08
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — galgil, Jun 25 '15 at 13:27

score 0 · Answer 1 · answered Jun 25 '15 at 13:03

0

Do it with UNPIVOT:

select * from TableName
unpivot([Values] for Fruits in ([Apples], [Mangoes], [Grapes]))u

answered Jun 25 '15 at 13:03

Giorgi Nakeuri

35,155
8
47
75

1

@jpw, is this prohibited? – Giorgi Nakeuri Jun 25 '15 at 13:05

Convert Data from Columns to Rows

1 Answers1