DB2 SQL Formatting

[yoyo]

Is there any way to make a select in DB2 to output in the following manner:

Attr1 value1
Attr2 value2
Attr3 value3
Attr4 value4

Instead of the

Attr1 Attr2 Attr3 Attr4
------- ------- ------- --------
value1 value2 value3 value4

Thanks for any help.

[Serge Rielau]

yoyo wrote:

Is there any way to make a select in DB2 to output in the following manner:

Attr1 value1
Attr2 value2
Attr3 value3
Attr4 value4

Instead of the

Attr1 Attr2 Attr3 Attr4
------- ------- ------- --------
value1 value2 value3 value4

Thanks for any help.

SELECT MAX(CASE WHEN c1 = 'Attr1' THEN c2 END) AS Attr1,
MAX(CASE WHEN c2 = 'Attr2' THEN c2 END) AS Attr2,
...
FROM T;
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Brian Tkatch]

SELECT 'Attr1', value1 FROM Table
UNION ALL
SELECT 'Attr2', value2 FROM Table
UNION ALL
SELECT 'Attr3', value3 FROM Table
UNION ALL
SELECT 'Attr4', value4 FROM Table
ORDER BY 1

[yoyo]

Brian Tkatch wrote:

SELECT 'Attr1', value1 FROM Table
UNION ALL
SELECT 'Attr2', value2 FROM Table
UNION ALL
SELECT 'Attr3', value3 FROM Table
UNION ALL
SELECT 'Attr4', value4 FROM Table
ORDER BY 1

Thanks, these both work, but I was hoping for an automatic way to so I
didn't have to type the names of the columns, some of these tables I'm
quering have over 100 attributes, and I need to repeat it on like 25 tables.
I wrote some php code and made a little query in a web page to format
the results for me. I dynamicaly select the colnames from syscat.columns
and then make the real select based on the colnames I got, select the
values and spit it out in html table. Works nice.
Thanks for the help, I'll remember these when I need them one day, and
I'm sure I will...

Ken

[Juliane via DBMonster.com]

Ken,

actually you answered the question yourself.
SQL is the query language; how to represent the data in your report is
another thing and done by the application itself. Exactly as you did.
Selecting the column names from syscat.columns would have been my suggestion
as well to automate.

Juliane
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200508/1

[Jurgen Haan]

yoyo wrote:

Thanks, these both work, but I was hoping for an automatic way to so I
didn't have to type the names of the columns, some of these tables I'm
quering have over 100 attributes, and I need to repeat it on like 25
tables.
I wrote some php code and made a little query in a web page to format
the results for me. I dynamicaly select the colnames from syscat.columns
and then make the real select based on the colnames I got, select the
values and spit it out in html table. Works nice.
Thanks for the help, I'll remember these when I need them one day, and
I'm sure I will...

Ken

For quick db2 scripting, I can recommend using perl in combination with
the DBM::DB2 extension. I've made some scripts which iterate through the
syscat.tables... One of them is to recreate views which are marked
invalid due to a table recreate.

-R-

[Brian Tkatch]

If this is something done over and over again, you could DECLARE a
TEMPORARY TABLE as wide as your widest TABLE, and INSERT INTO...SELECT
all COLUMNs, CASTing everything to an appropriate type. Then, have a
blanket routine take each COLUMN and INSERT it as another row.

Though as mentioned, SQL is a querying language, presentation is not
its forte.

[Tonkuma]

One of the problem of SQL query is the number of SELECTed columns must
be fixed before execution of the SQL query even using dynamic SQL.
So, if you want repeat a query statement on multiple tables, you need
some modification depending on the number of columns of each tables.
Following samples can be used for upto 20 columns tables.
Example1. Need modify comments lines as followings, because number of
columns of Employee table is 14.
And specify Tabschema and Tabname.
WITH
-- Target
(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c1 5,c16,c17,c18,c19,c20)
AS (
Target (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14) AS (
SELECT *
FROM Employee
WHERE empno = '000100'
)
SELECT
SUBSTR(sc.colname,1,18) colname
, SUBSTR(CHAR( c1),1, p1*LENGTH(CHAR( c1)))
||SUBSTR(CHAR( c2),1, p2*LENGTH(CHAR( c2)))
||SUBSTR(CHAR( c3),1, p3*LENGTH(CHAR( c3)))
||SUBSTR(CHAR( c4),1, p4*LENGTH(CHAR( c4)))
||SUBSTR(CHAR( c5),1, p5*LENGTH(CHAR( c5)))
||SUBSTR(CHAR( c6),1, p6*LENGTH(CHAR( c6)))
||SUBSTR(CHAR( c7),1, p7*LENGTH(CHAR( c7)))
||SUBSTR(CHAR( c8),1, p8*LENGTH(CHAR( c8)))
||SUBSTR(CHAR( c9),1, p9*LENGTH(CHAR( c9)))
||SUBSTR(CHAR(c10),1,p10*LENGTH(CHAR(c10)))
||SUBSTR(CHAR(c11),1,p11*LENGTH(CHAR(c11)))
||SUBSTR(CHAR(c12),1,p12*LENGTH(CHAR(c12)))
||SUBSTR(CHAR(c13),1,p13*LENGTH(CHAR(c13)))
||SUBSTR(CHAR(c14),1,p14*LENGTH(CHAR(c14)))
-- ||SUBSTR(CHAR(c15),1,p15*LENGTH(CHAR(c15)))
-- ||SUBSTR(CHAR(c16),1,p16*LENGTH(CHAR(c16)))
-- ||SUBSTR(CHAR(c17),1,p17*LENGTH(CHAR(c17)))
-- ||SUBSTR(CHAR(c18),1,p18*LENGTH(CHAR(c18)))
-- ||SUBSTR(CHAR(c19),1,p19*LENGTH(CHAR(c19)))
-- ||SUBSTR(CHAR(c20),1,p20*LENGTH(CHAR(c20)))
AS colvalue
FROM Target
, SYSCAT.COLUMNS sc
, (VALUES ( 0, 1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 1, 0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 2, 0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 3, 0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 4, 0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 5, 0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 6, 0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 7, 0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0)
,( 8, 0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0)
,( 9, 0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0)
,(10, 0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0)
,(11, 0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0)
,(12, 0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0)
,(13, 0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0)
,(14, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0)
,(15, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0)
,(16, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0)
,(17, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0)
,(18, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0)
,(19, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1)
) P
(n,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14, p15,p16,p17,p18,p19,p20)
WHERE sc.Tabschema = 'DB2ADMIN'
AND sc.Tabname = 'EMPLOYEE'
AND sc.Colno = p.n
ORDER BY
sc.Colno;
------------------------------------------------------------------------------

COLNAME COLVALUE
------------------ --------------------------------
EMPNO 000100
FIRSTNME THEODORE
MIDINIT Q
LASTNAME SPENSER
WORKDEPT E21
PHONENO 0972
HIREDATE 1980-06-19
JOB MANAGER
EDLEVEL 14
SEX M
BIRTHDATE 1956-12-18
SALARY 0026150.00
BONUS 0000500.00
COMM 0002092.00

14 record(s) selected.
Example2. Join with dummy table to make number of columns to 20.
------------------------------ Commands Entered
------------------------------
CREATE TABLE dummy_6
(c1 CHAR(1)
,c2 CHAR(1)
,c3 CHAR(1)
,c4 CHAR(1)
,c5 CHAR(1)
,c6 CHAR(1)
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
INSERT INTO dummy_6
VALUES
('', '', '', '', '', '');
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
WITH
Target
(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c1 5,c16,c17,c18,c19,c20)
AS (
SELECT e.*, dum.*
FROM Employee e
, dummy_6 dum
WHERE empno = '000100'
)
SELECT
SUBSTR(sc.colname,1,18) colname
, SUBSTR(CHAR( c1),1, p1*LENGTH(CHAR( c1)))
||SUBSTR(CHAR( c2),1, p2*LENGTH(CHAR( c2)))
||SUBSTR(CHAR( c3),1, p3*LENGTH(CHAR( c3)))
||SUBSTR(CHAR( c4),1, p4*LENGTH(CHAR( c4)))
||SUBSTR(CHAR( c5),1, p5*LENGTH(CHAR( c5)))
||SUBSTR(CHAR( c6),1, p6*LENGTH(CHAR( c6)))
||SUBSTR(CHAR( c7),1, p7*LENGTH(CHAR( c7)))
||SUBSTR(CHAR( c8),1, p8*LENGTH(CHAR( c8)))
||SUBSTR(CHAR( c9),1, p9*LENGTH(CHAR( c9)))
||SUBSTR(CHAR(c10),1,p10*LENGTH(CHAR(c10)))
||SUBSTR(CHAR(c11),1,p11*LENGTH(CHAR(c11)))
||SUBSTR(CHAR(c12),1,p12*LENGTH(CHAR(c12)))
||SUBSTR(CHAR(c13),1,p13*LENGTH(CHAR(c13)))
||SUBSTR(CHAR(c14),1,p14*LENGTH(CHAR(c14)))
||SUBSTR(CHAR(c15),1,p15*LENGTH(CHAR(c15)))
||SUBSTR(CHAR(c16),1,p16*LENGTH(CHAR(c16)))
||SUBSTR(CHAR(c17),1,p17*LENGTH(CHAR(c17)))
||SUBSTR(CHAR(c18),1,p18*LENGTH(CHAR(c18)))
||SUBSTR(CHAR(c19),1,p19*LENGTH(CHAR(c19)))
||SUBSTR(CHAR(c20),1,p20*LENGTH(CHAR(c20)))
AS colvalue
FROM Target
, SYSCAT.COLUMNS sc
, (VALUES ( 0, 1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 1, 0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 2, 0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 3, 0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 4, 0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 5, 0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 6, 0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 7, 0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0)
,( 8, 0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0)
,( 9, 0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0)
,(10, 0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0)
,(11, 0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0)
,(12, 0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0)
,(13, 0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0)
,(14, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0)
,(15, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0)
,(16, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0)
,(17, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0)
,(18, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0)
,(19, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1)
) P
(n,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14, p15,p16,p17,p18,p19,p20)
WHERE sc.Tabschema = 'DB2ADMIN'
AND sc.Tabname = 'EMPLOYEE'
AND sc.Colno = p.n
ORDER BY
sc.Colno;
------------------------------------------------------------------------------

COLNAME COLVALUE
------------------ ------------------------------
EMPNO 000100
FIRSTNME THEODORE
MIDINIT Q
LASTNAME SPENSER
WORKDEPT E21
PHONENO 0972
HIREDATE 1980-06-19
JOB MANAGER
EDLEVEL 14
SEX M
BIRTHDATE 1956-12-18
SALARY 0026150.00
BONUS 0000500.00
COMM 0002092.00

14 record(s) selected.
Example3. If some column(s) are null, you need some more complex
expressions for final SELECT list.
------------------------------ Commands Entered
------------------------------
WITH
Target
(c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c1 5,c16,c17,c18,c19,c20)
AS (
SELECT dpt.*, dum.*
FROM Department dpt
, dummy_15 dum
WHERE deptno = 'D01'
)
SELECT
SUBSTR(sc.colname,1,18) colname
, SUBSTR(COALESCE(CHAR( c1),''),1,(CASE WHEN c1 IS NOT NULL OR
p1=0 THEN p1 END)*LENGTH(COALESCE(CHAR( c1),'')))
||SUBSTR(COALESCE(CHAR( c2),''),1,(CASE WHEN c2 IS NOT NULL OR
p2=0 THEN p2 END)*LENGTH(COALESCE(CHAR( c2),'')))
||SUBSTR(COALESCE(CHAR( c3),''),1,(CASE WHEN c3 IS NOT NULL OR
p3=0 THEN p3 END)*LENGTH(COALESCE(CHAR( c3),'')))
||SUBSTR(COALESCE(CHAR( c4),''),1,(CASE WHEN c4 IS NOT NULL OR
p4=0 THEN p4 END)*LENGTH(COALESCE(CHAR( c4),'')))
||SUBSTR(COALESCE(CHAR( c5),''),1,(CASE WHEN c5 IS NOT NULL OR
p5=0 THEN p5 END)*LENGTH(COALESCE(CHAR( c5),'')))
||SUBSTR(COALESCE(CHAR( c6),''),1,(CASE WHEN c6 IS NOT NULL OR
p6=0 THEN p6 END)*LENGTH(COALESCE(CHAR( c6),'')))
||SUBSTR(COALESCE(CHAR( c7),''),1,(CASE WHEN c7 IS NOT NULL OR
p7=0 THEN p7 END)*LENGTH(COALESCE(CHAR( c7),'')))
||SUBSTR(COALESCE(CHAR( c8),''),1,(CASE WHEN c8 IS NOT NULL OR
p8=0 THEN p8 END)*LENGTH(COALESCE(CHAR( c8),'')))
||SUBSTR(COALESCE(CHAR( c9),''),1,(CASE WHEN c9 IS NOT NULL OR
p9=0 THEN p9 END)*LENGTH(COALESCE(CHAR( c9),'')))
||SUBSTR(COALESCE(CHAR(c10),''),1,(CASE WHEN c10 IS NOT NULL OR
p10=0 THEN p10 END)*LENGTH(COALESCE(CHAR(c10),'')))
||SUBSTR(COALESCE(CHAR(c11),''),1,(CASE WHEN c11 IS NOT NULL OR
p11=0 THEN p11 END)*LENGTH(COALESCE(CHAR(c11),'')))
||SUBSTR(COALESCE(CHAR(c12),''),1,(CASE WHEN c12 IS NOT NULL OR
p12=0 THEN p12 END)*LENGTH(COALESCE(CHAR(c12),'')))
||SUBSTR(COALESCE(CHAR(c13),''),1,(CASE WHEN c13 IS NOT NULL OR
p13=0 THEN p13 END)*LENGTH(COALESCE(CHAR(c13),'')))
||SUBSTR(COALESCE(CHAR(c14),''),1,(CASE WHEN c14 IS NOT NULL OR
p14=0 THEN p14 END)*LENGTH(COALESCE(CHAR(c14),'')))
||SUBSTR(COALESCE(CHAR(c15),''),1,(CASE WHEN c15 IS NOT NULL OR
p15=0 THEN p15 END)*LENGTH(COALESCE(CHAR(c15),'')))
||SUBSTR(COALESCE(CHAR(c16),''),1,(CASE WHEN c16 IS NOT NULL OR
p16=0 THEN p16 END)*LENGTH(COALESCE(CHAR(c16),'')))
||SUBSTR(COALESCE(CHAR(c17),''),1,(CASE WHEN c17 IS NOT NULL OR
p17=0 THEN p17 END)*LENGTH(COALESCE(CHAR(c17),'')))
||SUBSTR(COALESCE(CHAR(c18),''),1,(CASE WHEN c18 IS NOT NULL OR
p18=0 THEN p18 END)*LENGTH(COALESCE(CHAR(c18),'')))
||SUBSTR(COALESCE(CHAR(c19),''),1,(CASE WHEN c19 IS NOT NULL OR
p19=0 THEN p19 END)*LENGTH(COALESCE(CHAR(c19),'')))
||SUBSTR(COALESCE(CHAR(c20),''),1,(CASE WHEN c20 IS NOT NULL OR
p20=0 THEN p20 END)*LENGTH(COALESCE(CHAR(c20),'')))
AS colvalue
FROM Target
, SYSCAT.COLUMNS sc
, (VALUES ( 0, 1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 1, 0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 2, 0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 3, 0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 4, 0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 5, 0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 6, 0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0)
,( 7, 0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0)
,( 8, 0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0)
,( 9, 0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0)
,(10, 0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0)
,(11, 0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0)
,(12, 0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0)
,(13, 0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0)
,(14, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0)
,(15, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0)
,(16, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0)
,(17, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0)
,(18, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0)
,(19, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1)
) P
(n,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14, p15,p16,p17,p18,p19,p20)
WHERE sc.Tabschema = 'DB2ADMIN'
AND sc.Tabname = 'DEPARTMENT'
AND sc.Colno = p.n
ORDER BY
sc.Colno;
------------------------------------------------------------------------------

COLNAME COLVALUE
------------------ ---------------------------
DEPTNO D01
DEPTNAME DEVELOPMENT CENTER
MGRNO -
ADMRDEPT A00
LOCATION -

5 record(s) selected.