SQL Query to fetch data from same column?

Question

How can i fetch data from a single column according to their respective data_id for a and group_id ?

--------------------------------------------
id  |  group_id  |  data_id  |value 
--------------------------------------------
1          20          15        Product1
2          20           1         Name1
3          20           2       Location1
4          20           3        Price1  
5          20           4          Tag1
6          21          15         Product2
.
.
. and so on

Disered Output is :

--------------------------------------------
Product  |  Name  |  Location  |  Price  |  Tag
--------------------------------------------
Product1    Name1     Location1     Price1    Tag1
Product2    Name2     Location2     Price2    Tag2

Update if data_id=15 AND value = 'Product1' then all the values related to Product1 should be fetched from the data base, if data_id=15 AND value = 'Product2' then all the values related to Product2 should be fetched

If you can make a better database i would do it. http://databases.about.com/od/specificproducts/a/normalization.htm — Denzyl Dick, Jul 17 '14 at 10:32
Hi Puneet, Your update isn't clear. Can you elaborate a bit more? — Himanshu, Jul 17 '14 at 12:22

score 1 · Answer 1 · answered Jul 17 '14 at 10:30

1

It can be done, but this is not a GOOD design at all. You should have 5 tables, one for each data_id, then use join query to retrieve what you want.

But here is a query for your bad design:

select product, name, ... from
(select group_id as g1 value as product from bad_table where data_id = 15),
(select group_id as g2 value as name from bad_table where data_id = 1), 
...
where g1 = g2 and ...;

answered Jul 17 '14 at 10:30

Hamid Alaei

406
4
16

if every production has exactly one name, one location, one price, and one tag, then actually the output is a good design – Hamid Alaei Jul 17 '14 at 10:38

score 1 · Accepted Answer · edited May 23 '17 at 12:32

You can achieve this using a GROUP_CONCAT() function. However as @Hamid Alaei said this is not a good design.

If data_id remains same for any value e.g. for value "Product" data_id is always 15 then you can try this:

SELECT 
      GROUP_CONCAT(CASE WHEN `data_id` = 15 
                        THEN `value` ELSE NULL END) AS Product
      ,GROUP_CONCAT(CASE WHEN `data_id` = 1 
                         THEN `value` ELSE NULL END) AS Name
      ,GROUP_CONCAT(CASE WHEN `data_id` = 2 
                         THEN `value` ELSE NULL END) AS Location
      ,GROUP_CONCAT(CASE WHEN `data_id` = 3
                         THEN `value` ELSE NULL END) AS Price
      ,GROUP_CONCAT(CASE WHEN `data_id` = 4
                         THEN `value` ELSE NULL END) AS Tag
FROM Table1
GROUP BY `group_id`;

Otherwise try this:

SELECT 
      GROUP_CONCAT(CASE WHEN `value` LIKE 'Product%' 
                        THEN `value` ELSE NULL END) AS Product
      ,GROUP_CONCAT(CASE WHEN `value` LIKE 'Name%' 
                         THEN `value` ELSE NULL END) AS Name
      ,GROUP_CONCAT(CASE WHEN `value` LIKE 'Location%' 
                         THEN `value` ELSE NULL END) AS Location
      ,GROUP_CONCAT(CASE WHEN `value` LIKE 'Price%' 
                         THEN `value` ELSE NULL END) AS Price
      ,GROUP_CONCAT(CASE WHEN `value` LIKE 'Tag%' 
                         THEN `value` ELSE NULL END) AS Tag
FROM Table1
GROUP BY `group_id`

Result (of the given table):

|  PRODUCT |   NAME |  LOCATION |  PRICE |    TAG |
|----------|--------|-----------|--------|--------|
| Product1 |  Name1 | Location1 | Price1 |   Tag1 |
| Product2 | (null) |    (null) | (null) | (null) |

See this SQLFiddle

For those who are coming here from search this query worked for me .. @hims056 +1 for the respond.. (sorry replying for this late. ;) ) — puneet, Nov 19 '16 at 18:22

score 0 · Answer 3 · answered Jul 17 '14 at 10:25

select (select product from table group by product) as product , 
(select name from table group by name) as name,
(select location from table group by location) as location,
(select price from table group by price) as price , 
(select tag from table group by tag) as tag from table

SQL Query to fetch data from same column?

3 Answers3

See this SQLFiddle