Show rows data into column format

Question

I have data in mysql data base in following format-

itemName      Date      isAvailable
abc        1/12/2012    yes
xyz        1/12/2012    No
aaa        1/12/2012    yes

abc        2/12/2012    no
xyz        2/12/2012    no
aaa        2/12/2012    yes

abc        3/12/2012    no
xyz        3/12/2012    yes
aaa        3/12/2012    yes
new        3/12/2012    yes

I want to collect data into following format-

itemName   1/12/2012    2/12/2012   3/12/2012
abc       yes             no        no
xyz       no              no        yes
aaa       yes             yes       yes
new        -              -         yes

Any help?

Hi Indra, welcom to SO. Can you show us some code that demonstrates the way you arrive at this format? — Ben, Dec 28 '12 at 06:48
Look at this http://stackoverflow.com/questions/11899647/select-dynamic-columns-in-mysql — Walery Strauch, Dec 28 '12 at 06:50
You need a query that outputs that data in the format you need and then you need to execute that query into result set and then loop over your result set — Andrew, Dec 28 '12 at 06:59
@Steve i m looking for query which will give me that format afte — Indra Yadav, Dec 28 '12 at 07:02
Your output table contains only four columns or it changes dynamically — Saharsh Shah, Dec 28 '12 at 07:45
Please see Oracle SQL example here: http://stackoverflow.com/questions/14065815/get-the-database-table-values-in-matrix-format — Art, Dec 28 '12 at 15:17

score 0 · Accepted Answer · answered Dec 28 '12 at 08:06

0

Try this one:

SELECT itemName,
       GROUP_CONCAT(CASE Date_format(`Date`,'%Y-%d-%m') WHEN '2012-01-12' THEN `isAvailable` ELSE NULL END) AS `2012-01-12`
      ,GROUP_CONCAT(CASE Date_format(`Date`,'%Y-%d-%m') WHEN '2012-02-12' THEN `isAvailable` ELSE NULL END) AS `2012-02-12`
      ,GROUP_CONCAT(CASE Date_format(`Date`,'%Y-%d-%m') WHEN '2012-03-12' THEN `isAvailable` ELSE NULL END) AS `2012-03-12`
FROM Table1
GROUP BY `itemName`;

See this SQLFiddle

answered Dec 28 '12 at 08:06

Himanshu Jansari

30,115
28
106
129

thanks... your code is working but there is little bit change in my data i have N/A value and it is showing as 0 – Indra Yadav Dec 28 '12 at 10:07
@IndraYadav - N/A value in which column? Show your exact data (few) in [this SQLFiddle](http://sqlfiddle.com/#!2/20e4c/12) – Himanshu Jansari Dec 28 '12 at 10:10

score 0 · Answer 2 · edited May 23 '17 at 11:56

0

You are talking about a PIVOT operation when you transform rows into columns. Here is an example on SO.

Some DBMS will have special commands to deal with that (Like SQl Server that has PIVOT and UNPIVOT). I dont know if mysql has anything.

If you google "T-SQL PIVOT" you will find several examples on how to do it using just T_SQL

edited May 23 '17 at 11:56

Community

1
1

answered Dec 28 '12 at 10:41

Diego

33,213
18
87
131

Show rows data into column format

2 Answers2

See this SQLFiddle