1

I have mutiple columns and I want to create a dynamic query which can return column name and each of their distinct values. I want to do this in MySQl

So if I have only 2 columns:

  1. Gender which has Male and Female
  2. Purchase_Frequency with Low, Medium and High

In that case the output should be

Entity                Unique_value
gender                 Male
gender                 Female
Purchase_Frequency     Low
Purchase_Frequency     Medium
Purchase_Frequency     High

I found similar questions but didn't help me much

Return column name and distinct values

Return column names and count of their distinct values in MySQL

GMB
  • 216,147
  • 25
  • 84
  • 135
feyorn
  • 13
  • 3

1 Answers1

0

You can use union all:

select distinct 'gender' entity, gender unique_value from mytable
union all
select distinct 'purchase_frequency', purchase_frequency from mytable

Note that union all requires the subqueries to return columns that have the same datatype. While this would work here (since all columns appear to be string), that's something to keep in mind if you ever need to extend this query with more columns (explicit casting might be needed).

GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thanks for this GMB, however is there is a way to make this process dynamic, if I don't wish to a separate line for each column, is there a way for that? – feyorn May 28 '20 at 12:42
  • @feyorn: no. You would need to use dynamic SQL for this, which is a rather different beast. You might want to ask a new question for this. – GMB May 28 '20 at 12:44