Sql Server & SSRS Tips

 Break comma-separated string/column/parameter into multiple rows in MYSQL. with recursive     cs_string as ( select 'a,b,c,d' as items),     cs_rowID as ( select 1 as n union select n + 1 from cs_rowID, cs_string         where n <= length(items) - length(replace(items, ',', ''))) select distinct substring_index(substring_index(items, ',', n), ',', -1)  group_name from cs_rowID, cs_string Output:

 mySQL search query on multiple columns with a single parameter. set @search='32' ; select DISTINCT p.id as product_id, p.name as title, p.sku, p.description,     p.material,c.name as color, ct.name as category from  products p inner join color_product cp on p.id=cp.product_id inner join colors c on cp.color_id=c.id inner join categories ct on p.category_id=ct.id where p.name  LIKE CONCAT("%",@search,"%") OR   p.sku  LIKE CONCAT("%",@search,"%") OR       p.description  LIKE CONCAT("%",@search,"%") OR       p.material  LIKE CONCAT("%",@search,"%") OR       c.name   LIKE CONCAT("%",@search,"%") OR       ct.name   LIKE CONCAT("%",@search,"%") ;

 To combine rows as comma separated for a column instead of multiple rows we will use GROUP_CONCAT function with that column, but all the remaining columns will be in group by clause. below is the query for products with multiple colors for a single product. we will combine colors column as comma separated in the below query. select DISTINCT p.id as product_id , p.name as title, p.sku, p.material,  p.feature_image_path, feature_image_name ,                 GROUP_CONCAT(c.name) color  -- it will combile multi values as comma seperated rather than multiple rows but remaining columns will be in group by from  products p inner join color_product cp on p.id=cp.product_id inner join colors c on cp.color_id=c.id inner join categories ct on p.category_id=ct.id where p.name  LIKE CONCAT("%",'red',"%") OR   p.sku  LIKE CONCAT("%",'red',"%") OR       p.description  LIKE CONCAT("%",'red',"%"...