Select individual values ​​for multiple columns

Question

I have a table in which many pieces of data correspond to one in another column, like a tree, and then the data in the “sheet” for each specific sheet

eg,

Food Group Name Caloric Value Vegetables Broccoli 100 Vegetables Carrots 80 Fruits Apples 120 Fruits Bananas 120 Fruits Oranges 90 

I would like to design a query that will only return individual values ​​of each column and then null to overflow the overflow

eg,

 Food group Name Caloric Value Vegetables Broccoli 100 Fruit Carrots 80 Apples 120 Bananas 90 Oranges 

I'm not sure if this is possible, right now I was trying to do it with chores, however I was hoping there would be an easier way

Answer 1

It sounds like you're just trying to have all the different values. What for? To display goals? This is an application, not a server. You could just ask three queries:

 SELECT DISTINCT [Food Group] FROM atable; SELECT DISTINCT Name FROM atable; SELECT DISTINCT [Caloric Value] FROM atable; 

and display their results accordingly.

But if you insist that they all be in the same table, you can try the following:

 WITH atable ([Food Group], Name, [Caloric Value]) AS ( SELECT 'Vegetables', 'Broccoli', 100 UNION ALL SELECT 'Vegetables', 'Carrots', 80 UNION ALL SELECT 'Fruits', 'Apples', 120 UNION ALL SELECT 'Fruits', 'Bananas', 120 UNION ALL SELECT 'Fruits', 'Oranges', 90 ), atable_numbered AS ( SELECT [Food Group], Name, [Caloric Value], fg_rank = DENSE_RANK() OVER (ORDER BY [Food Group]), n_rank = DENSE_RANK() OVER (ORDER BY Name), cv_rank = DENSE_RANK() OVER (ORDER BY [Caloric Value]) FROM atable ) SELECT fg.[Food Group], n.Name, cv.[Caloric Value] FROM ( SELECT fg_rank FROM atable_numbered UNION SELECT n_rank FROM atable_numbered UNION SELECT cv_rank FROM atable_numbered ) r (rank) LEFT JOIN ( SELECT DISTINCT [Food Group], fg_rank FROM atable_numbered) fg ON r.rank = fg.fg_rank LEFT JOIN ( SELECT DISTINCT Name, n_rank FROM atable_numbered) n ON r.rank = n.n_rank LEFT JOIN ( SELECT DISTINCT [Caloric Value], cv_rank FROM atable_numbered) cv ON r.rank = cv.cv_rank ORDER BY r.rank 

Answer 2

I guess I would like to know why this is needed in one result set? What does the code that will use this result look like? The attributes in each row have nothing to do with each other. If you want to, say, create the contents of a set of drop-down lists, you'd better do it at a time. In the requested result set, you will need to iterate over the data set every three times to do something useful, and you need to either check NULL each time, or unnecessarily iterate over all the paths to the end of the data set.

If this is in a stored procedure, you cannot run three separate SELECT DISTINCTs and return the values ​​as three results. Then you can consume them one at a time, what would you do anyway, I would suggest.

If there is a REALLY connection between the values, you can add each of the results to an array or list, and then access all three lists in parallel using the index.

Answer 3

Something like this maybe?

  select *
 from (
   select case 
           when row_number () over (partition by fruit_group) = 1 then fruit_group
           else null 
         end as fruit_group,
         case 
           when row_number () over (partition by name) = 1 then name
           else null
         end as name,
         case 
           when row_number () over (partition by caloric) = 1 then caloric
           else null
         end as caloric
   from your_table
 ) t
 where fruit_group is not null
    or name is not null
    or caloric is not null

But I do not see the point in this