Categorize item according to their types

user_19373379 0
Viewed 53

I have an inventory table in database that shows id, code, description and class1. It looks a bit like this:

  id code  description          class1
 --------------------------------------
  1  001   Office stationary     101   
  2  002   Computer stationary   101                    
  3  003   Documents             101                                    
  4  001   Surgical products     107                            
  5  002   Urological products   107

How do I categorize them according to class1? What condition should I use?

I wanted the output to look more like this:

101 - 001 Office stationary - 002 Computer stationary - 003 Documents

107 - 001 Surgical products - 002 Urological products

sql
2 Answers

I’m fairly novice at sql but you would want to do a s SELECT x WHERE class1 = 101 repeat the query but for 107 if you want to omit a row you will need to do another command either MID LEFT OR RIGHT. If you want assorted by numerical order you could do that too.

user_167099990

Here is an ms sql solution

WITH DATA (id,code,description,class1) as(
select  1,  '001',   'Office stationary',     101   union all
select  2,  '002',   'Computer stationary',   101   union all                 
select  3,  '003',   'Documents',             101   union all                        
select  4,  '001',   'Surgical products',     107   union all       
select  5,  '002',   'Urological products',   107
)
select concat(d.class1,' - ',STRING_AGG(concat(d.code,' ',d.description),' - ')WITHIN GROUP (ORDER BY d.code)) a
from DATA d
group by d.class1

enter image description here

user_23101540
Related