• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

SQL Query

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
amitabht
Asked:
amitabht
1 Solution
 
Umar Topia.Net Full Stack DeveloperCommented:
you can use PIVOT... UNPIVOT keywords in SQL Server to achieve the transpose
0
 
Shaju KumbalathDeputy General Manager - ITCommented:
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
 
amitabhtAuthor Commented:
I'm using MYSQL. And there is no PIVOT function there. Sorry, i was not clear in my question.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
gajmpCommented:
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
 
Shaju KumbalathDeputy General Manager - ITCommented:
@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
 
Shaju KumbalathDeputy General Manager - ITCommented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
amitabhtAuthor Commented:
This answer or link had given me right direction to think.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now