DB Query for Pivot Post: 303022208

Sponsored Content

Top Forums Programming DB Query for Pivot Post 303022208 by durden_tyler on Monday 27th of August 2018 12:51:42 PM

08-27-2018

Registered User

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:

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

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...

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...

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 ...

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

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...

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 ...

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/...

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...

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...

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...

LEARN ABOUT SUNOS

update-rcconf-guide

UPDATE-RCCONF-GUIDE(8)						 Debian GNU/Linux					    UPDATE-RCCONF-GUIDE(8)

NAME

       update-rcconf-guide - Create default guide file for rcconf

SYNOPSIS

       update-rcconf-guide

DESCRIPTION

       Update-rcconf-guide creates the default guide file which rcconf uses.

       Update-rcconf-guide searches the package names corresponding to each service file in /etc/init.d directory from dpkg info
       files(/var/lib/dpkg/info/*.list) and get the description of these packages using apt-cache. Update-rcconf-guide uses Short-Description in
       priority to the description if service files has Short-Description field.  Update-rcconf-guide writes those results to
       /var/lib/rcconf/guide.default file.

       You can write your own guide in user guide file(/var/lib/rcconf/guide) by hand.	Rcconf refers Guides in /var/lib/rcconf/guide before those
       in /var/lib/rcconf/guide.default.

       If you install some packages after executed update-rcconf-guide, you need to re-create this file using update-rcconf-guide so as to refresh
       guide.default that includes new guides for installed new services.

       Notice: update-rcconf-guide was not executed when you installed rcconf
	       package.

FILE

       /var/lib/rcconf/guide.default
	       Guide File update-rcconf-guide generates.

       /var/lib/rcconf/guide
	       Guide File user(Administrator) can define.

SEE ALSO

       rcconf(8)

AUTHOR

       Atsushi KAMOSHIDA <kamop@debian.org>

perl v5.10.1							    2009-01-21						    UPDATE-RCCONF-GUIDE(8)