Pivot or Invert row data

[tdavidge]

Folks,

Given a table that might contain the rows:

DATE ID
==== ==
1/1/2001 1A
1/1/2001 2B
1/1/2001 3C
1/1/2001 4D
1/2/2001 1A
1/2/2001 2B
1/2/2001 3C
1/2/2001 4D
1/3/2001 1A
1/3/2001 2B
1/3/2001 3C
1/3/2001 4D
1/4/2001 1A
1/4/2001 2B
1/4/2001 3C
1/4/2001 4D

How then do I get a single query, table function or otherwise to
display said table as:

1/1/2001 1/2/2001 1/3/2001 1/4/2001
======== ======== ======== ========
1A 1A 1A 1A
2B 2B 2B 2B
3C 3C 3C 3C
4D 4D 4D 4D
I can get the individual columns to display, but cannot combine to show
all.

Any bright ideas on how I can do this outside of the Data Warehouse
transformers ?

Many thanks

[Tonkuma]

> I can get the individual columns to display, but cannot combine to show all.

What do you mean combine?
Could you show us the combined result image?

Did you already got the following result? And do you want get another
result?
Or, do you ask how to get the following image?

1/1/2001 1/2/2001 1/3/2001 1/4/2001
======== ======== ======== ========
1A 1A 1A 1A
2B 2B 2B 2B
3C 3C 3C 3C
4D 4D 4D 4D

[tdavidge]

Tonkuma wrote:

I can get the individual columns to display, but cannot combine to show all.

What do you mean combine?
Could you show us the combined result image?

Did you already got the following result? And do you want get another
result?
Or, do you ask how to get the following image?

1/1/2001 1/2/2001 1/3/2001 1/4/2001
======== ======== ======== ========
1A 1A 1A 1A
2B 2B 2B 2B
3C 3C 3C 3C
4D 4D 4D 4D

Indeed I am asking how to display the above 4 column image in one sql
query at the sametime. Sorry for the confusion. I can never get more
than one dates column to display at a time with falling into full
scalar select problems or something similar.

[Tonkuma]

I assumed date format is mm/dd/yyyy.
Although, following sample is very complex and it may be simplified. It
worked on DB2 UDB for Windows V8.

------------------------------ Commands Entered
------------------------------
WITH
Add_rn1 AS (
SELECT date
, ROWNUMBER() OVER(ORDER BY date) rn1
FROM (SELECT DISTINCT DATE FROM tdavi) Q(date)
)
,Add_rn2 AS (
SELECT t.*, rn1
, ROWNUMBER() OVER(PARTITION BY t.date ORDER BY t.id) rn2
FROM tdavi t
, Add_rn1 n
WHERE t.date = n.date
)
,Recurse (ln, rn1, id) AS (
SELECT 1 ln
, rn1
, id
FROM Add_rn2
WHERE rn2 = 1
UNION ALL
SELECT pre.ln + 1
, pre.rn1
, new.id
FROM Recurse pre
, Add_rn2 new
WHERE pre.ln < 10000
AND pre.rn1 = new.rn1
AND new.rn2 = pre.ln + 1
)
SELECT col1 AS " ", col2 AS " ", col3 AS " ", col4 AS " "
FROM (
SELECT -1 ln
, MAX(CASE rn1 WHEN 1 THEN CHAR(date,USA) END)
, MAX(CASE rn1 WHEN 2 THEN CHAR(date,USA) END)
, MAX(CASE rn1 WHEN 3 THEN CHAR(date,USA) END)
, MAX(CASE rn1 WHEN 4 THEN CHAR(date,USA) END)
FROM Add_rn1
UNION ALL
VALUES (0, '==========', '==========', '==========',
'==========')
UNION ALL
SELECT ln
, MAX(CASE rn1 WHEN 1 THEN id END)
, MAX(CASE rn1 WHEN 2 THEN id END)
, MAX(CASE rn1 WHEN 3 THEN id END)
, MAX(CASE rn1 WHEN 4 THEN id END)
FROM Recurse
GROUP BY ln
) Q (ln, col1, col2, col3, col4)
ORDER BY ln;
------------------------------------------------------------------------------

---------- ---------- ---------- ----------
01/01/2001 01/02/2001 01/03/2001 01/04/2001
========== ========== ========== ==========
1A 1A 1A 1A
2B 2B 2B 2B
3C 3C 3C 3C
4D 4D 4D 4D

6 record(s) selected.

[Tonkuma]

I assumed date format is mm/dd/yyyy, and the values of DATE are not
known before access the table.

WITH
Add_rn AS (
SELECT date
, id
, DENSERANK() OVER(ORDER BY date) rn1
, ROWNUMBER() OVER(PARTITION BY date ORDER BY id) rn2
FROM tdavi
)
SELECT col1 AS " ", col2 AS " ", col3 AS " ", col4 AS " "
FROM (
SELECT -1
, MAX(CASE rn1 WHEN 1 THEN CHAR(date,USA) END)
, MAX(CASE rn1 WHEN 2 THEN CHAR(date,USA) END)
, MAX(CASE rn1 WHEN 3 THEN CHAR(date,USA) END)
, MAX(CASE rn1 WHEN 4 THEN CHAR(date,USA) END)
FROM (SELECT DISTINCT date
, rn1
FROM Add_rn) Q
UNION ALL
VALUES (0, '==========', '==========', '==========',
'==========')
UNION ALL
SELECT rn2
, MAX(CASE rn1 WHEN 1 THEN id END)
, MAX(CASE rn1 WHEN 2 THEN id END)
, MAX(CASE rn1 WHEN 3 THEN id END)
, MAX(CASE rn1 WHEN 4 THEN id END)
FROM Add_rn
GROUP BY rn2
) Q (ln, col1, col2, col3, col4)
ORDER BY ln;
------------------------------------------------------------------------------
---------- ---------- ---------- ----------
01/01/2001 01/02/2001 01/03/2001 01/04/2001
========== ========== ========== ==========
1A 1A 1A 1A
2B 2B 2B 2B
3C 3C 3C 3C
4D 4D 4D 4D

6 record(s) selected.

[tdavidge]

Thanks for that Tonkuma, that works very nicely when modified for my
tables. I appreciate the assistance.

One question, how well would this scale depending on how many distinct
dates are in the table, and what if I knew what they were prior to
needing to run the query ? I know I'm asking a lot, but would it be
able to scale to say an entire year ?

Again, my thanks for your help .. it's much appreciated.

Tim

[Serge Rielau]

Tim,

This is a rather strange table. All your IDs have the same values and
there is no real data. I'll take a gamble here with an extended example
hoping I don't lead you off-topic:

CREATE TABLE CHIPTRUCK_SALES
(LOCATION VARCHAR(20),
DATE DATE,
HOTDOGS_SOLD INT);

INSERT INTO CHIPTRUCK_SALES VALUES
('Claremont', '1/1/2001', 10),
('Brougham', '1/1/2001', 20),
('Greenwood', '1/1/2001', 8),
('Whitevale', '1/1/2001', 5),
('Claremont', '1/2/2001', 11),
('Brougham', '1/2/2001', 22),
('Greenwood', '1/2/2001', 15),
('Whitevale', '1/2/2001', 17),
('Claremont', '1/3/2001', 8),
('Brougham', '1/3/2001', 19),
('Greenwood', '1/3/2001', 12),
('Whitevale', '1/3/2001', 7);

SELECT
LOCATION,
MAX(CASE WHEN date = '1/1/2001' THEN hotdogs_sold END) AS "1/1/2001",
MAX(CASE WHEN date = '1/2/2001' THEN hotdogs_sold END) AS "1/2/2001",
MAX(CASE WHEN date = '1/3/2001' THEN hotdogs_sold END) AS "1/3/2001"
FROM CHIPTRUCK_SALES GROUP BY LOCATION ORDER BY LOCATION;

=>
LOCATION 1/1/2001 1/2/2001 1/3/2001
-------------------- ----------- ----------- -----------
Brougham 20 22 19
Claremont 10 11 8
Greenwood 8 15 12
Whitevale 5 17 7

4 record(s) selected.

A true PIVOT would flip columsn and rows I presume:

DROP TABLE CHIPTRUCK_SALES;

CREATE TABLE CHIPTRUCK_SALES
(DATE DATE, BROUGHAM INT, CLAREMONT INT, GREENWOOD INT, WHITEVALE INT);

INSERT INTO CHIPTRUCK_SALES VALUES
('1/1/2001', 20, 10, 8, 5),
('1/2/2001', 22, 11, 15, 17),
('1/3/2001', 19, 8, 12, 7);

SELECT * FROM CHIPTRUCK_SALES;

DATE BROUGHAM CLAREMONT GREENWOOD WHITEVALE
---------- ----------- ----------- ----------- -----------
01/01/2001 20 10 8 5
01/02/2001 22 11 15 17
01/03/2001 19 8 12 7

3 record(s) selected.

SELECT
LOCATION,
MAX(CASE WHEN date = '1/1/2001' THEN hotdogs_sold END) AS "1/1/2001",
MAX(CASE WHEN date = '1/2/2001' THEN hotdogs_sold END) AS "1/2/2001",
MAX(CASE WHEN date = '1/3/2001' THEN hotdogs_sold END) AS "1/3/2001"

FROM CHIPTRUCK_SALES,
TABLE(VALUES('Brougham', BROUGHAM),
('Claremont', CLAREMONT),
('Greenwood', GREENWOOD),
('Whitevale', WHITEVALE))
AS SALES(LOCATION, HOTDOGS_SOLD)
GROUP BY LOCATION ORDER BY LOCATION;

LOCATION 1/1/2001 1/2/2001 1/3/2001
--------- ----------- ----------- -----------
Brougham 20 22 19
Claremont 10 11 8
Greenwood 8 15 12
Whitevale 5 17 7

4 record(s) selected.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Tonkuma]

Theoretically, the number of distinct dates is limited by "Most
elements in a select list"(=500(4Kpage) or =1012(>=8Kpage)) according
to "SQL Reference Volume 1 Version 8.2, Appendix A. SQL limits Table
33".
But, I don't know the practical limit. I feel at least some tens of
columns would be not so problem.

A shortcoming of this way(or SQL syntax itself) is need to determine(or
know) the number of columns in a select list and code them in a SQL
before execute the SQL and access the table.
It might be better to code some more columns in the SQL than estimated
distinct dates. Data of extra columns may be NULL.