0

Let me take straight away a concrete example. I have one table that look like this:

id,john,jack,jimy
 1,22,NULL,NULL
 2,NULL,55,66

I would like to know if it is possible with an sql to change that table to the following table:

id,person,value
 1,john,22
 2,jack,55
 3,jimy,66
Andriy M
  • 76,112
  • 17
  • 94
  • 154
Marc
  • 9,217
  • 21
  • 67
  • 90

2 Answers2

1

If you are using SQL Server, you can use UNPIVOT:

select ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), VALUE, PERSON
from Table1
unpivot (value for person in (john,jack,jimy)) A

SQL Fiddle

UPDATE

When I provided this solution, there was no tags to guide me. I don't think this solution will work in MySQL, then you could try:

 select 1, 'John' Person, max(John) Value from Table1 UNION
 select 2, 'Jack' Person, max(Jack) Value from Table1 UNION
 select 3, 'Jimy' Person, max(Jimy) Value from Table1

As showed in this SQL Fiddle

Nizam
  • 4,569
  • 3
  • 43
  • 60
  • ty that is a good hint but do you think it will work with mySQL? – Marc Jul 23 '14 at 15:45
  • The output provided by your SQL Server suggestion doesn't exactly match the OP's expected output, does it? Please consider removing it. – Andriy M Jul 25 '14 at 07:14
  • @AndriyM You mean because the ID was not distinct for each person? The new ID keep no relation with the old one, right? IF so, I've made adjustments to my query to return sequential IDs for each person. – Nizam Jul 25 '14 at 11:29
0

Portable SQL:

select 1 as id, 'john' as person, max(john) as value from yourtable
union all
select 2 as id, 'jack' as person, max(jack) as value from yourtable
union all
select 3 as id, 'jimy' as person, max(jimy) as value from yourtable

Replace max with an aggregate of your choice in case there are multiple non-NULL values in any column.

0xF
  • 3,214
  • 1
  • 25
  • 29