[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query

Posted on 2011-10-21
9
Medium Priority
?
306 Views
Last Modified: 2012-05-12
Hi,
  I want to write a query where list of products ( varies between 10 to 50) should come as a column. Meaning transpose the row value into column. Result shoul look like

                               Product_A   Product_B   .. ... Product_Z
This_month sales       10              15                          7
Prev_month sales       5                  4                          9.
I need help only in transposing the data. Rest i know about count and all. Hope to find some smart SQL from this group.
0
Comment
Question by:amitabht
8 Comments
 
LVL 10

Expert Comment

by:Umar Topia
ID: 37010346
you can use PIVOT... UNPIVOT keywords in SQL Server to achieve the transpose
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37010421
Example (Oracle 11g)

create table sales (product varchar2(10), sales_dt date,sales_value number(12,2));

Insert into SALES
   (PRODUCT, SALES_DT, SALES_VALUE)
 Values
   ('A', TO_DATE('08/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into SALES
   (PRODUCT, SALES_DT, SALES_VALUE)
 Values
   ('A', TO_DATE('08/23/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200);
Insert into SALES
   (PRODUCT, SALES_DT, SALES_VALUE)
 Values
   ('A', TO_DATE('09/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 350);
Insert into SALES
   (PRODUCT, SALES_DT, SALES_VALUE)
 Values
   ('B', TO_DATE('10/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 500);
Insert into SALES
   (PRODUCT, SALES_DT, SALES_VALUE)
 Values
   ('B', TO_DATE('09/22/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200);
COMMIT;


select * from (
   select TO_CHAR(SALES_DT,'MM-YYYY'), PRODUCT,SUM(SALES_VALUE) SALES_VALUE
   from SALES t GROUP BY SALES_DT, PRODUCT
)
pivot
(
   SUM(SALES_VALUE)
   for PRODUCT in ('A','B')  -- specify the distinct product codes
)

order by SALES_DT;


0
 

Author Comment

by:amitabht
ID: 37010467
I'm using MYSQL. And there is no PIVOT function there. Sorry, i was not clear in my question.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 3

Expert Comment

by:gajmp
ID: 37010676
shajukg: and umartopia:
In PIVOT and UNPIVOT we have to specify all the products. As per the Author, list of products varies between 10 to 50. So how can we use PIVOT and UNPIVOT.
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37010749
@gajmp

in that case only way out in Oracle is to use  "Pivot xml" there u can use the "ANY" clause or a subquery.

eg:

select * from (
   select TO_CHAR(SALES_DT,'MM-YYYY'), PRODUCT,SUM(SALES_VALUE) SALES_VALUE
   from SALES t GROUP BY SALES_DT, PRODUCT
)
pivot xml
(
   SUM(SALES_VALUE)
   for PRODUCT in (ANY)
)

order by SALES_DT;
0
 
LVL 15

Expert Comment

by:Shaju Kumbalath
ID: 37010758
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 37025005
there is no way to do this dynamically in MySQL alone.
so, you need to know how to formulate the sql:
http://en.wikibooks.org/wiki/MySQL/Pivot_table

and then eventually have a front-end application to generate the needed sql.
if you have some reporting tool, it might be much easier, as those tools know how to pivot "out of the box"
0
 

Author Closing Comment

by:amitabht
ID: 37380959
This answer or link had given me right direction to think.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 6 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question