Home » SQL & PL/SQL » SQL & PL/SQL » conversion of row data to separate columns (Oracle,SQL)
conversion of row data to separate columns [message #319122] Fri, 09 May 2008 02:09 Go to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
I had written a sql query of Sales register, wherein i want some output data in rows to appear as separate column headings. And under that column headings the other row data should come.

The Query is given below :
SELECT
INVH_NO INV,
INVH_DT INV_DT,
BILLTO.ADDR_NAME PARTY_NAME,
SHIPTO.ADDR_NAME CONSIGNEE,
INVI_ITEM_DESC PRODUCT_NAME,
INVI_QTY,
INVI_RATE RATE,
INVDD_FC_VAL INVAMT,
ITED_TED_CODE,
ITED_UNROUND_FC_AMT,
ITED_FC_AMT,
INVH_FLEX_01 PO_NO,
TO_DATE(INVH_FLEX_11, 'DDMMYYYY') PO_DATE,
INVH_FLEX_08 DESTINATION,
INVH_FLEX_09 TRANSPORT
FROM
OT_INVOICE_HEAD,
OT_INVOICE_ITEM,
OT_INVOICE_BATCH,
OT_INVOICE_DUE_DT,
OT_INVOICE_ITEM_TED,
OM_ADDRESS SHIPTO,
OM_ADDRESS BILLTO
WHERE INVI_INVH_SYS_ID = INVH_SYS_ID
AND INVB_INVI_SYS_ID = INVI_SYS_ID
AND OT_INVOICE_DUE_DT.INVDD_INVH_SYS_ID=OT_INVOICE_HEAD.INVH_SYS_ID
AND INVI_SYS_ID = ITED_I_SYS_ID
AND INVH_SYS_ID = ITED_H_SYS_ID
AND SHIPTO.ADDR_CODE = INVH_SHIP_TO_ADDR_CODE
AND BILLTO.ADDR_CODE = INVH_BILL_TO_ADDR_CODE

The remaining details are explained in attachment.

Kindly give me the solution.

  • Attachment: OUTPUT.JPG
    (Size: 37.13KB, Downloaded 77 times)
Re: conversion of row data to separate columns [message #319189 is a reply to message #319122] Fri, 09 May 2008 05:11 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
simple decode will help you
Re: conversion of row data to separate columns [message #319258 is a reply to message #319189] Fri, 09 May 2008 08:52 Go to previous messageGo to next message
yogeshyl
Messages: 50
Registered: October 2007
Location: Silvassa
Member
Kindly explain. Can you do it for me. I dont know how to use the decode statement.
Re: conversion of row data to separate columns [message #319271 is a reply to message #319258] Fri, 09 May 2008 09:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DECODE

Regards
Michel
Re: conversion of row data to separate columns [message #319275 is a reply to message #319122] Fri, 09 May 2008 09:52 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
try this:

Kevin Meades OraFAQ Blog

Example of Data Pivots in SQL (rows to columns and columns to rows)

Kevin
Previous Topic: Small Help
Next Topic: Need SQL Query
Goto Forum:
  


Current Time: Sat Dec 03 14:24:35 CST 2016

Total time taken to generate the page: 0.07303 seconds