Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: DB Query for Pivot
Top Forums Programming DB Query for Pivot Post 303022208 by durden_tyler on Monday 27th of August 2018 12:51:42 PM
Old 08-27-2018
Quote:
Originally Posted by Perlbaby
...
With below results in Db2 v10.5 . Please refer column A and B are same,while Staus column defers with distinct values .


A B STATUS
Insert Update Old
Insert Update New
Insert Update Final


Can someone guide how to achieve this
A B Status
Insert Update Old
Null or Blank New
Null or Blank Final


Column A and B should be show same value . The o/p values are dynamic. ...
Not sure if I understood the "o/p values are dynamic" part, but see if this helps:

Code:
SQL>
SQL> SELECT * FROM mytable;

A          B          STATUS
---------- ---------- ----------
Insert     Update     Old
Insert     Update     New
Insert     Update     Final

3 rows selected.

SQL>
SQL> -- Using CASE expression
SQL> SELECT CASE status WHEN 'Old' THEN a ELSE NULL END AS a,
  2         CASE status WHEN 'Old' THEN b ELSE NULL END AS b,
  3         status
  4    FROM mytable
  5  ;

A          B          STATUS
---------- ---------- ----------
Insert     Update     Old
                      New
                      Final

3 rows selected.

SQL>
SQL> -- Using DECODE function
SQL> SELECT DECODE(status, 'Old', a, NULL) AS a,
  2         DECODE(status, 'Old', b, NULL) AS b,
  3         status
  4    FROM mytable
  5  ;

A          B          STATUS
---------- ---------- ----------
Insert     Update     Old
                      New
                      Final

3 rows selected.

SQL>
SQL>

This User Gave Thanks to durden_tyler For This Post:
jim mcnamara
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

pivot

I have a sql table with : Acitvity Date Value ABC 7/11 10 DEF 7/11 98 ABC 7/12 23 DEF 7/12 100 SER 7/12 67 GRH 7/13 123 HJY 7/14 12 I... (4 Replies)
Discussion started by: mukhanj
4 Replies

2. Shell Programming and Scripting

Pivot table

Hello everyone, I would like to use awk to parse a file with three columns in, like: monday 0 1 monday 1 1 monday 2 1 monday 3 1 monday 4 1 monday 5 1 tuesday 0 5 tuesday 1 1 tuesday 2 1 tuesday 3 1 tuesday 4 1 wednesday 0 1 monday 5 25 they represent the day the hour and the... (2 Replies)
Discussion started by: gio001
2 Replies

3. Shell Programming and Scripting

Pivot file contents

Hi All, I am trying to pivot the contents in a file. Ex: I have a file sample.txt with data "A B C D", i need the contents to pivot & resulting file should look like "A B C ... (3 Replies)
Discussion started by: new_ds_man
3 Replies

4. Shell Programming and Scripting

Create Pivot table

I would like to use awk to parse a file with three columns in, like: Chennai,01,1 Chennai,07,1 Chennai,08,3 Chennai,09,6 Chennai,10,12 Chennai,11,19 Chennai,12,10 Chennai,13,12 Kerala,09,2 AP,10,1 AP,11,1 Delhi,13,1 Kerala,13,3 Chennai,00,3 Chennai,01,1 Chennai,02,1 Chennai,07,5 (3 Replies)
Discussion started by: boston_nilesh
3 Replies

5. Shell Programming and Scripting

Pivot script

Hi Please suggest a script that would do a horizontal pivot , on the fields separated by a semicolon Below is my input file 1|c2|aa 1|c3|dd 1|c4|cc 1|c5|aa 1|c6|ss 1|c7|dd 1|c8|bb 1|c9|jjj 1|c10|kkk 1|c11|fffg 1|c12|nnn;indi;pak;linf;wer 1|c13|lllnk;li;sdfsd;oiuo 1|c14|ppp... (5 Replies)
Discussion started by: mabarif16
5 Replies

6. UNIX for Dummies Questions & Answers

Pivot multiple

Dear friend, I want to sum popul based on ville and reg. input date country ville reg popul 20131101 INDIA Gujarat College 322047286 20131101 USA Oregon 2 Kindergaten 477305599 20131101 INDIA Delhi 1 Ecole 255029428 20131101 MEXICO ... (2 Replies)
Discussion started by: radius
2 Replies

7. Shell Programming and Scripting

Pivot using awk

Hi, I am writing a code to basically pivot the data. awk -v var1="" -v var2="" -v var3="" -v var4="" -v var5="" -v Disp=0\ 'BEGIN {FS=":"; OFS="|";}\ /^Pattern1/ {var1=$2;Disp=0;} \ /^Pattern2/ {var2=$2;} \ /^Pattern3/ {var3=$2;} \ /^Pattern4/ {var4=$2;} \ /^Pattern5/... (5 Replies)
Discussion started by: tostay2003
5 Replies

8. Shell Programming and Scripting

Pivot file using shellscript

Hi, I am new at using shell scripting. So I have a question for the more experienced Linux users. I would like to perform some kind of pivot on a file. Example Input file COL1,"2001-01-01","2001-03-01","2001-03-24" A1,22,,44 B1,56,78,12 C2,5,, I would like to have to following output... (2 Replies)
Discussion started by: shanlinux
2 Replies

9. UNIX for Beginners Questions & Answers

Data Pivot

Good Day, I have file input 6285296582710|20170509|INDOTEL 6285296835209|20170509|INDOTEL 6285296940311|20170509|INDOTEL 6285297027737|20170509|MULTIFLAG 6285297027737|20170509|DELTA 6285297304373|20170510|INDOTEL 6285297384129|20170510|INDOTEL 6285296940311|20170510|MULTIFLAG... (2 Replies)
Discussion started by: radius
2 Replies

10. Shell Programming and Scripting

Pivot example

Hi all, I am new to shell scripting so pardon me for the questions I will be asking. I was given a task where I have to pivot my data Example Source SGPAPCTUMACCHEA Expected output SGP APC TUM SGP APC ACC SGP APC HEA Can anybody assist me on this?Please use CODE tags as required... (3 Replies)
Discussion started by: redaela
3 Replies

LEARN ABOUT SUSE

sql::statement::term

SQL::Statement::Term(3) 				User Contributed Perl Documentation				   SQL::Statement::Term(3)

NAME

       SQL::Statement::Term - base class for all terms

SYNOPSIS

	 # create a term with an SQL::Statement object as owner
	 my $term = SQL::Statement::Term->new( $owner );
	 # access the value of that term
	 $term->value( $eval );

DESCRIPTION

       SQL::Statement::Term is an abstract base class providing the interface for all terms.

INHERITANCE

	 SQL::Statement::Term

METHODS

   new
       Instantiates new term and stores a weak reference to the owner.

   value
       Abstract method which will return the value of the term. Must be overridden by derived classes.

   DESTROY
       Destroys the term and undefines the weak reference to the owner.

NAME

       SQL::Statement::ConstantTerm - term for constant values

SYNOPSIS

	 # create a term with an SQL::Statement object as owner
	 my $term = SQL::Statement::ConstantTerm->new( $owner, 'foo' );
	 # access the value of that term - returns 'foo'
	 $term->value( $eval );

DESCRIPTION

       SQL::Statement::ConstantTerm implements a term which will always return the same constant value.

INHERITANCE

	 SQL::Statement::ConstantTerm
	 ISA SQL::Statement::Term

METHODS

   new
       Instantiates new term and stores the constant to deliver and a weak reference to the owner.

   value
       Returns the specified constant.

NAME

       SQL::Statement::ColumnValue - term for column values

SYNOPSIS

	 # create a term with an SQL::Statement object as owner
	 my $term = SQL::Statement::ColumnValue->new( $owner, 'id' );
	 # access the value of that term - returns the value of the column 'id'
	 # of the currently active row in $eval
	 $term->value( $eval );

DESCRIPTION

       SQL::Statement::ColumnValue implements a term which will return the specified column of the active row.

INHERITANCE

	 SQL::Statement::ColumnValue
	 ISA SQL::Statement::Term

METHODS

   new
       Instantiates new term and stores the column name to deliver and a weak reference to the owner.

   value
       Returns the specified column value.

AUTHOR AND COPYRIGHT

       Copyright (c) 2009,2010 by Jens Rehsack: rehsackATcpan.org

       All rights reserved.

       You may distribute this module under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl
       README file.

perl v5.12.1							    2010-05-06						   SQL::Statement::Term(3)
All times are GMT -4. The time now is 03:27 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy