On Wednesday, March 8, 2023, Rosebrock, Uwe (Environment, Hobart) <
uwe.rosebr...@csiro.au> wrote:
> Hi List
>
> I like to use crosstab to pivot a long list of rows into columns, however
> the column labels are created dynamically with the resulting column type
> known.
&
Hi List
I like to use crosstab to pivot a long list of rows into columns, however the
column labels are created dynamically with the resulting column type known.
Is there a way pass a list of names and types to crosstab (‘query’) as (<‘type
list as strings in array’> ) ?
In other words,
On Sat, Mar 4, 2023 at 10:05 PM Ron wrote:
>
> Ugh. It's a long and hairy query that would be a nightmare in a format
> statement.
>
>
Assuming you can pass this thing into the crosstab function in the first
place you must already have put it into a string. Changing &
On 3/4/23 19:32, David G. Johnston wrote:
On Sat, Mar 4, 2023 at 5:20 PM Ron wrote:
But crosstab takes text strings as parameters. How then do you use \set
variables in crosstab queries?
You need to dynamically write the textual query you want to send to the
crosstab function. In
dentifiers. Therefore, a construction such as ':foo' doesn't work to
produce a quoted literal from a variable's value.
"
But crosstab takes text strings as parameters. How then do you use \set
variables in crosstab queries?
If you read a little further, you'll find out the s
On Sat, Mar 4, 2023 at 5:20 PM Ron wrote:
>
> But crosstab takes text strings as parameters. How then do you use \set
> variables in crosstab queries?
>
>
You need to dynamically write the textual query you want to send to the
crosstab function. In particular that means w
s. Therefore, a construction such as ':foo' doesn't work to
> produce a quoted literal from a variable's value.
> "
> But crosstab takes text strings as parameters. How then do you use \set
> variables in crosstab queries?
If you read a little further, you&#
7;:foo' doesn't work to
produce a quoted literal from a variable's value.
"
$ psql12 -v BOM=2023-02-01 -af foo.sql
select :'BOM'::timestamp + interval'6 month';
?column?
-
2023-08-01 00:00:00
(1 row)
select $$ :BOM $$;
?col
)
>>>
>>>
>>>
>>
>> This is basically your issue - specifying the items you want as
>> individual items in an IN construct instead of making them into a set (in
>> this case an array so the set is compactif
g them into a set (in this case an
> array so the set is compactified into a single value):
>
> Something like:
>
> WITH sids (sid_array) AS (
> SELECT ARRAY[
>'.',
>''.
>etc...
> ]
> )
> SELECT usids.sid, ct.*
> FROM (SELECT unnest(sid_array) AS sid FROM sids) AS usids (sid)
> LEFT JOIN crosstab( format($$SELECT ... s.s_id = ANY(%L) ...$$, (SELECT
> sids.sid_array::text FROM sids) ) AS ct ( pop text, text, etc... ) ON
> usids.sid = ct.pop
>
> David J.
>
>
y so the set is compactified into a single value):
Something like:
WITH sids (sid_array) AS (
SELECT ARRAY[
'.',
''.
etc...
]
)
SELECT usids.sid, ct.*
FROM (SELECT unnest(sid_array) AS sid FROM sids) AS usids (sid)
LEFT JOIN crosstab( format($$SELECT ... s.s_id = ANY(%L) ...$$, (SELECT
sids.sid_array::text FROM sids) ) AS ct ( pop text, text, etc... ) ON
usids.sid = ct.pop
David J.
Here you go:
SELECT *
FROM crosstab(
'SELECT s.s_n AS Pop
, ad.a_d_y::text AS Yr
, ad.s_a_qty::text --for some Pop all of
these are null for every Yr
FROM st
On 12/24/2022 9:03 PM, David Goldsmith wrote:
How do I force "empty rows" to be included in my query output? (I've
tried LEFT JOINing to the row header results, and using CASE
statements; but due to my unfamiliarity w/ using crosstab, I'm not
sure if I've used those
Hi. New subscriber and intermediate level SQL writer here, still pretty new
to Postgresql (but I don't know how to do the following in TSQL either).
I've figured out how to write a crosstab query I need; the problem is that
the number of row results should be 72, but I'm only g
Hi
st 6. 1. 2021 v 21:47 odesílatel Daniel Verite
napsal:
> Pavel Stehule wrote:
>
> > > *That* is a function of how Postgres set returning functions work, and
> not
> > > specific to crosstab(). It is not easily fixed. Patches to fix that
>
(resent to the list, previous post was rejected)
Pavel Stehule wrote:
> > *That* is a function of how Postgres set returning functions work, and not
> > specific to crosstab(). It is not easily fixed. Patches to fix that would
> > be
> > welcomed!
> >
&
Hi
út 5. 1. 2021 v 19:45 odesílatel Joe Conway napsal:
> On 1/5/21 12:11 PM, Tim Clarke wrote:
> > imho the crosstab() function isn't a good implementation. The biggest
> failure it
> > has is that you must know exactly how many output columns you will have
> in
On 1/5/21 10:46 AM, Adam Tauno Williams wrote:
> I'm using the crosstab feature and do not understand why I am only
> getting values in the first column.
> So I put this in as a crosstab:
>
> SELECT * FROM crosstab(
> $$
> $$) AS final_result (invoice_da
On 1/5/21 12:11 PM, Tim Clarke wrote:
> imho the crosstab() function isn't a good implementation. The biggest failure
> it
> has is that you must know exactly how many output columns you will have in the
> result /_prior to running it
*That* is a function of how Postgres set re
Adam Tauno Williams schrieb am 05.01.2021 um 16:46:
I'm using the crosstab feature and do not understand why I am only
getting values in the first column.
The query:
SELECT
date_trunc('month', t2.value_date) AS invoice_date,
t1.value_string AS invoice_type
COUNT(*)
effort than it puts
out.
From the documentation:
"""
The crosstab function produces one output row for each consecutive group of
input rows with the same row_name value. It fills the output value columns,
left to right, with the value fields from these rows. If there are fewer
On Tue, Jan 5, 2021 at 8:46 AM Adam Tauno Williams
wrote:
> Only the first column has values, all the rest are NULL.
> I assume I am missing something stupid.
>
I think you are assigning the function more intelligence/effort than it
puts out.
>From the documentation:
""&
I'm using the crosstab feature and do not understand why I am only
getting values in the first column.
The query:
SELECT
date_trunc('month', t2.value_date) AS invoice_date,
t1.value_string AS invoice_type
COUNT(*)
FROM document d
LEFT OUTER JOIN obj_property t1
Professor Mueller! I believe that we met, long ago. I graduated from your
department in 1984 where I worked closely with the wonderful, late Prof.
Dipple.
Postgres.app is a very easy way to work with Postgres, and it does include
support for tablefunc. If you ever want to check which extensions ar
On 2/26/19 8:10 PM, Martin Mueller wrote:
Thank you for the prompt and clear answer. I work with a Mac (OS 10.4.3) and I
used the PostgresApp to install it. I am a very primitive user of Postgres and
think of it as Microsoft Access on Steroids. I access it via Aqua Data, Studio,
but I don't th
On 2/26/19 7:51 PM, Martin Mueller wrote:
I run Postgres 10.5. I understand that there is something called
tablefunc and it includes a crosstab function. On Stack Overflow I learn
that you import this function. But from where and how? The Postgres
documentation is quite clear and
5:51 PM, Martin Mueller wrote:
> I run Postgres 10.5. I understand that there is something called
> tablefunc and it includes a crosstab function. On Stack Overflow I learn
> that you import this function. But from where and how? The Postgres
> documentation is quite
26/19, 8:04 PM, "Adrian Klaver" wrote:
On 2/26/19 5:51 PM, Martin Mueller wrote:
> I run Postgres 10.5. I understand that there is something called
> tablefunc and it includes a crosstab function. On Stack Overflow I learn
> that you import this function. But
>>>>> "Martin" == Martin Mueller writes:
Martin> I run Postgres 10.5. I understand that there is something
Martin> called tablefunc and it includes a crosstab function. On Stack
Martin> Overflow I learn that you import this function. But from where
Martin>
On 2/26/19 5:51 PM, Martin Mueller wrote:
I run Postgres 10.5. I understand that there is something called
tablefunc and it includes a crosstab function. On Stack Overflow I learn
that you import this function. But from where and how? The Postgres
documentation is quite clear and
I run Postgres 10.5. I understand that there is something called tablefunc and
it includes a crosstab function. On Stack Overflow I learn that you import this
function. But from where and how? The Postgres documentation is quite clear
and intelligible to a retired English professor like me
31 matches
Mail list logo
