Datastage,IBM Datatsage,Unix, Datatsage Interview Questions and Scenarios,Unix Commands,Datastage Admin,Datatsage Interview Questions,Sql Interview Questions
Monday, February 13, 2023
PIVOT function to transform the data from rows to columns SQL
Using the PIVOT
function to transform the data from rows to columns
-------------------------------
| Id | Value | ColumnName |
-------------------------------
| 1 | John | FirstName |
| 2 | 2.4 | Amount |
| 3 | ZH1E4A | PostalCode |
| 4 | Fork | LastName |
| 5 | 857685 | AccountNumber |
-------------------------------
This is my result:
---------------------------------------------------------------------
| FirstName |Amount| PostalCode | LastName | AccountNumber |
---------------------------------------------------------------------
| John | 2.4 | ZH1E4A | Fork | 857685 |
---------------------------------------------------------------------
query :-
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;
CASE
expression:
select
max(case when columnname = 'FirstName' then value end) Firstname,
max(case when columnname = 'Amount' then value end) Amount,
max(case when columnname = 'PostalCode' then value end) PostalCode,
max(case when columnname = 'LastName' then value end) LastName,
max(case when columnname = 'AccountNumber' then value end) AccountNumber
from yourtable
Subscribe to:
Posts (Atom)
python
import datetime import pandas as pd import cx_Oracle con = cx_Oracle.connect( 'SYSTEM/oracle123@localhost:1521/xe' ) c = con.c...
-
DataStage: Hierarchical Data Stage Transforming JSON Data Hierarchical Data Stage can parse, compose and transform hierarchical data such ...
-
Introduction: In this section, we will see different clients available as part of DataStage installation and how to connect to them a...