Forum: >>> Magnum BBS <<<

[Info-ingres] PIVOT table

From Martin Bowes@21:1/5 to All on Fri Jan 22 12:36:13 2021

Hi All,

Can the 'in-value-list' part of the PIVOT() syntax take a simple select rather than a hard coded list of values?

sql iidbdb << SQL_END
DECLARE GLOBAL TEMPORARY TABLE session.p(
pid integer4,
field_id integer4,
value integer4
) ON COMMIT PRESERVE ROWS WITH NORECOVERY;

INSERT INTO session.p VALUES
(1, 100, 17),
(1, 101, 18),
(1, 102, 8),

(2, 100, 10),
(2, 101, 9),
(2, 102, 7);

SELECT * FROM session.p
ORDER by pid, field_id;

DECLARE GLOBAL TEMPORARY TABLE session.itm AS
SELECT DISTINCT field_id
FROM session.p
ON COMMIT PRESERVE ROWS WITH NORECOVERY;

\p\g
/*
** This pivot works, but the hard coded list would prove annoying if I had to ** mention say 20,000 different field_ids
*/
SELECT pid, pv.*
FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv
ORDER BY pid;
\p\g
/*
** This pivot gives a syntax error, but would be nicer to allow for expansion of
** the field_ids.
*/
SELECT pid, pv.*
FROM session.p PIVOT (MAX(value) FOR field_id IN (select field_id from session.itm)) AS pv
ORDER BY pid;
\p\g
\q
SQL_END

Martin Bowes

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)"> <style>
</head>
<body lang="EN-GB" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
Hi All,<o:p></o:p>
<o:p> </o:p>
Can the ‘in-value-list’ part of the PIVOT() syntax take a simple select rather than a hard coded list of values?<o:p></o:p>
<o:p> </o:p>
sql iidbdb << SQL_END<o:p></o:p>
DECLARE GLOBAL TEMPORARY TABLE session.p(<o:p></o:p>
    pid         integer4,<o:p></o:p>
    field_id integer4,<o:p></o:p>
    value    integer4<o:p></o:p>
) ON COMMIT PRESERVE ROWS WITH NORECOVERY;<o:p></o:p>
<o:p> </o:p>
INSERT INTO session.p VALUES<o:p></o:p>
    (1, 100, 17),<o:p></o:p>
    (1, 101, 18),<o:p></o:p>
    (1, 102, 8),<o:p></o:p>
<o:p> </o:p>
    (2, 100, 10),<o:p></o:p>
    (2, 101, 9),<o:p></o:p>
    (2, 102, 7);<o:p></o:p>
<o:p> </o:p>
SELECT * FROM session.p<o:p></o:p>
ORDER by pid, field_id;<o:p></o:p>
<o:p> </o:p>
DECLARE GLOBAL TEMPORARY TABLE session.itm AS<o:p></o:p>
SELECT DISTINCT field_id<o:p></o:p>
FROM session.p<o:p></o:p>
ON COMMIT PRESERVE ROWS WITH NORECOVERY;<o:p></o:p>
<o:p> </o:p>
\p\g<o:p></o:p>
/*<o:p></o:p>
** This pivot works, but the hard coded list would prove annoying if I had to<o:p></o:p>
** mention say 20,000 different field_ids<o:p></o:p>
*/<o:p></o:p>
SELECT pid, pv.*<o:p></o:p>
FROM session.p PIVOT (MAX(value) FOR field_id IN (100, 101, 102)) AS pv<o:p></o:p>
ORDER BY pid;<o:p></o:p>
\p\g<o:p></o:p>
/*<o:p></o:p>
** This pivot gives a syntax error, but would be nicer to allow for expansion of<o:p></o:p>
** the field_ids.<o:p></o:p>
*/<o:p></o:p>
SELECT pid, pv.*<o:p></o:p>
FROM session.p PIVOT (MAX(value) FOR field_id IN (select field_id from session.itm)) AS pv<o:p></o:p>
ORDER BY pid;<o:p></o:p>
\p\g<o:p></o:p>
\q<o:p></o:p>
SQL_END<o:p></o:p>
<o:p> </o:p>
Martin Bowes<o:p></o:p>
</div>
</body>
</html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Who's Online
Recent Visitors
- Michal Wronka
  Wed Apr 24 14:13:57 2024
  from Wroclaw, Poland via SSH
- Michal Wronka
  Wed Apr 24 14:02:51 2024
  from Wroclaw, Poland via SSH
- Michal Wronka
  Thu Apr 25 14:02:21 2024
  from Wroclaw, Poland via SSH
- Bob Worm
  Thu Apr 25 11:52:12 2024
  from Wales, Uk via Telnet

System Info

Sysop:	Keyop
Location:	Huddersfield, West Yorkshire, UK
Users:	296
Nodes:	16 (2 / 14)
Uptime:	54:30:45
Calls:	6,650
Calls today:	2
Files:	12,200
Messages:	5,330,627

[Info-ingres] PIVOT table

Who's Online

Recent Visitors

System Info