Visit The New, Modern Unix Linux Community


DB Query for Pivot


 
Thread Tools Search this Thread
Top Forums Programming DB Query for Pivot
# 1  
DB Query for Pivot

hi team


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. please guide
# 2  
Usually, sql implementations come with some formatting features. Here, I'd suggest a "break column". Specify like break on A on B mayhap with a skip n to highlight a value change in A or B .
# 3  
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:

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #369
Difficulty: Medium
The name bytecode originates from instruction sets that have zero-byte opcodes followed by optional parameters.
True or False?

10 More Discussions You Might Find Interesting

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

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

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

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

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

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

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

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

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

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

Featured Tech Videos