Build a SQL Query to get Data in row form (all products name in row in-front one category)

Question

Need to build a SQL Query

I have two tables

tbl_cat
_________________
cat_id | cat_name

tbl_pro
__________________________
pro_id | pro_name | cat_id

now I need data in form of row

e.g

category name | pro name 1 | pro name 2 | pro name 3

OR

Category name | pro name 1 , pro name 2 , pro name 3

my Query is

SELECT ct.cat_name,
       cp.proname
FROM tbl_cat ct
JOIN tbl_pro cp
WHERE ct.cat_id=cp.cat_id;

and it shows result like this

_____________________________
cat Name | Pro Name 
cat Name | Pro Name 1 
cat Name | Pro Name 2

Please visit http://stackoverflow.com/help/how-to-ask and read the guidelines here for how to ask a good question. Also, be sure to add your code so we can see what exactly has been done, and what exactly may need to be fixed or added. Thank you in advance. — EastOfJupiter, May 06 '16 at 15:02
try to improve my question i think this is better than before — Zaheer Ahmad, May 06 '16 at 15:24
Get your answer here http://stackoverflow.com/questions/15154644/group-by-to-combine-concat-a-column — Akanksha Singh, May 06 '16 at 15:34
Unrelated, but: `JOIN tbl_pro cp WHERE ct.cat_id=cp.cat_id;` is invalid SQL. If you use `JOIN` you need to put the join condition in the `ON` clause. `JOIN tbl_pro cp ON ct.cat_id=cp.cat_id;` — , May 09 '16 at 08:22

score 0 · Answer 1 · answered May 06 '16 at 15:12

0

$result = mysql_query"
SELECT tbl_cat.CatName, tbl_pro.ProName1, tbl_pro.ProName2, tbl_pro.ProName3
FROM tbl_cat
LEFT JOIN tbl_pro
ON tbl_cat.cat_id=tbl_pro.cat_id
";

while($row = mysql_fetch_array($result)) {
 echo "Category: " . $row[CatName] . " Product1: " . $row[ProName1] . " Product2: " . $row[ProName2] . " Product3: " . $row[ProName3];
}

answered May 06 '16 at 15:12

stackunderflow

422
2
5
19

thanks but i need it only in sql i know it easily handle in php :) but once again thank you very much – Zaheer Ahmad May 06 '16 at 15:25

Sam · Answer 2 · 2016-05-09T07:17:53.793

0

If you are using sqlserver2008 R2 or above Try this:

select ct.cat_name,
    STUFF(
             (SELECT DISTINCT ',' + cp.pro_name
              FROM tbl_pro cp INNER JOIN tbl_cat ci on ci.cat_id=cp.cat_id
              where ci.cat_id = ct.cat_id                 
              FOR XML PATH (''))
              , 1, 1, '')  AS ProductList
    FROM tbl_cat ct
    GROUP BY ct.cat_name,ct.cat_id

edited May 09 '16 at 07:17

answered May 09 '16 at 07:10

Sam

2,935
1
19
26

score 0 · Answer 3 · answered May 09 '16 at 08:21

0

You didn't state your DBMS, so this is for Postgres:

SELECT ct.cat_name, string_agg(cp.proname, ',')
FROM tbl_cat ct
  JOIN tbl_pro cp ON ct.cat_id=cp.cat_id
GROUP BY ct.cat_name
ORDER BY ct.cat_name;

answered May 09 '16 at 08:21

Build a SQL Query to get Data in row form (all products name in row in-front one category)

3 Answers3