Dynamic bar for thousands of columns
I am using pgAdmin III / PostgreSQL 9.4 to store and work with my data. An example of my current data:
x | y
--+--
0 | 1
1 | 1
2 | 1
5 | 2
5 | 2
2 | 2
4 | 3
6 | 3
2 | 3
How do I format it:
1, 2, 3
- the column names are unique values y
0, 5, 4
- the first matching values x
1, 5, 6
- the second value x
2, 2, 2
- etc.
It needs to be dynamic because I have millions of rows and thousands of unique values for y
.
Is there a dynamic support bar approach for this? I have not been able to successfully implement this:
DECLARE @columns VARCHAR(8000)
SELECT @columns = COALESCE(@columns + ',[' + cast(y as varchar) + ']',
'[' + cast(y as varchar)+ ']')
FROM tableName
GROUP BY y
DECLARE @query VARCHAR(8000)
SET @query = '
SELECT x
FROM tableName
PIVOT
(
MAX(x)
FOR [y]
IN (' + @columns + ')
)
AS p'
EXECUTE(@query)
It stops at the first line and gives an error:
syntax error at or near "@"
All of the dynamic rotation examples I've seen use this, so I'm not sure what I did wrong. Any help is appreciated. Thank you for your time.
** Note. It is important that the values are x
stored in the correct order as sequence matters. I can add another column to indicate the sequential order if needed.
source to share
First , when you say "first row", you are assuming a natural row order that is not found in the database tables. So yes, you do add another column to indicate sequential order
, as you already suspected. I am taking a column for this purpose . - If you don't want the poor man's default with the last (unreliable) resort: tbl_id
ctid
Further , the code you provided looks like MS SQL Server code, which is completely invalid for Postgres.
Finally , millions of rows and thousands of unique values for Y
it would even be pointless for it to try to return individual columns. Postgres has generous limits, but not generous enough for that. Quoting Postgres "About" :
Maximum columns for a table 250 - 1600 depending on column types
Thus, we are not even able to discuss the limiting characteristics of SQL, which requires knowing the columns and their data types at runtime, rather than dynamically adjusting them at runtime. Thus, you will need two separate calls , as we discussed in detail on this issue.
You will also find alternative return arrays by @Clodoaldo on the same question. It can be completely dynamic . And what I propose here, too. The request is actually pretty simple:
WITH cte AS (
SELECT *, row_number() OVER (PARTITION BY y ORDER BY tbl_id) AS rn
FROM tbl
ORDER BY y, tbl_id
)
SELECT text 'y' AS col, array_agg (y) AS values
FROM cte
WHERE rn = 1
UNION ALL
( -- parentheses required
SELECT text 'x' || rn, array_agg (x)
FROM cte
GROUP BY rn
ORDER BY rn
);
Result:
col | values
----+--------
y | {1,2,3}
x1 | {0,5,4}
x2 | {1,5,6}
x3 | {2,2,2}
Explanation
-
CTE calculates row_number
rn
for each row (eachx
) for each group ofy
. We will use it twice, hence CTE. -
The first one
SELECT
in the outer query generates an array of valuesy
. -
The second
SELECT
in the outer query generates all arrays of valuesx
in order. Arrays can be of different lengths.
Why do you need parentheses for UNION ALL
?
source to share