CODEWITHSUNDEEP
mysql
semilogo97
semilogo97

Reputation: 115

SQL Query to retrieve Row Values greater than 1 and Corresponding Column Names

I have this MYSQL (phpmydamin) database table that looks like this, and I'm trying to retrieve information on the values greater than 1 for a specific row as well as the column names corresponding with those set values. I'm basically trying to find out what item is held by a student and how many.

username      item1 item2 item3 item4 item5 item6 item7

n000111         1      0     1     1    1     1     1

n010554         0      0     3     2    0     0     0

n010555         1      0     4     0    0     1     1

n010556         0      0     0     0    0     0     0

n010557         0      8     0     2    0     1     0

So for example, if i'm sending the username "n010555", to my phpscript to fetch data from this table, I'm expecting feedback like:

n010555
item1 - 1
item3 - 4
item6 - 1
item7 - 1
total - 7

Something like that, I'll really appreciate the help. Thanks in advance.

Upvotes: 0

Views: 337

Answers (1)

Jacobm001
Jacobm001

Reputation: 4539

What you need to do is unpivot your data. Unfortunately, your data is stored in MySQL, which doesn't have such a feature.

This can be accomplished using a cross join, but it's not particularly efficient. If you don't have a huge amount of data, this may not be an issue, but if you have a nontrivial amount of data you may need to consider ETLing the data or changing your storage design.

Bluefeet has a good answer on how you can accomplish this. An adaptation for your solution would look something like:

select t.id,
  c.col,
  case c.col
    when 'item1' then item1
    when 'item2' then item2
    when 'item3' then item3
    when 'item4' then item4
    when 'item5' then item5
    when 'item6' then item6
    when 'item7' then item7
  end as data
from yourtable t
cross join
(
  select 'item1' as col
  union all select 'item2'
  union all select 'item3'
  union all select 'item4'
  union all select 'item5'
  union all select 'item6'
  union all select 'item7'
) c
where
  username = :username

Upvotes: 1

Related Questions