Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- A | B | C
- a1| b1| c1
- a1| b2| c2
- a1| b3| c3
- a2| b1| c2
- a2| b2| c5
- a1 | (c1,b1) ; (c2,b2);(c3;b3)
- a2 | (c2,b1) ; (c5,b2)
- An error occurred when executing the SQL command:
- CREATE FUNCTION cut_semicolon(text) RETURNS text AS $$
- BEGIN
- RETURN SUBSTRING($1 FROM 4)
- ERROR: unterminated dollar-quoted string at or near "$$
- BEGIN
- RETURN SUBSTRING($1 FROM 4)"
- Position: 53
- CREATE FUNCTION cut_semicolon(text) RETURNS text AS $$
- ^
- Execution time: 0.24s
- (Statement 1 of 7 finished)
- 0 rows affected
- END executed successfully
- Execution time: 0.22s
- (Statement 2 of 7 finished)
- An error occurred when executing the SQL command:
- $$ LANGUAGE 'plpgsql' IMMUTABLE
- ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE 'plpgsql' IMMUTABLE"
- Position: 1
- $$ LANGUAGE 'plpgsql' IMMUTABLE
- ^
- Execution time: 0.22s
- (Statement 3 of 7 finished)
- An error occurred when executing the SQL command:
- CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS $$
- BEGIN
- RETURN $1 || ' ; ' || $2
- ERROR: unterminated dollar-quoted string at or near "$$
- BEGIN
- RETURN $1 || ' ; ' || $2"
- Position: 62
- CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS $$
- ^
- Execution time: 0.22s
- (Statement 4 of 7 finished)
- 0 rows affected
- END executed successfully
- Execution time: 0.22s
- (Statement 5 of 7 finished)
- An error occurred when executing the SQL command:
- $$ LANGUAGE 'plpgsql' IMMUTABLE
- ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE 'plpgsql' IMMUTABLE"
- Position: 1
- $$ LANGUAGE 'plpgsql' IMMUTABLE
- ^
- Execution time: 0.22s
- (Statement 6 of 7 finished)
- An error occurred when executing the SQL command:
- CREATE AGGREGATE concat_semicolon(
- BASETYPE=text,
- SFUNC=concat_semicolon,
- STYPE=text,
- FINALFUNC=cut_semicolon,
- INITCOND=''
- )
- ERROR: SQL command "CREATE AGGREGATE concat_semicolon(
- BASETYPE=text,
- SFUNC=concat_semicolon,
- STYPE=text,
- FINALFUNC=cut_semicolon,
- INITCOND=''
- )" not supported.
- Execution time: 0.23s
- (Statement 7 of 7 finished)
- 5 statements failed.
- Script execution finished
- Total script execution time: 1.55s
- select A, concat(concat(concat(C, ',' ) , cast(B as varchar)), ',')
- from my_table
- group by A,B,C
- -- Is it ok to group by all A,B, C - since I can't group by A alone, which removes the related "C" columns--
- gives -:
- a1 c1b1b2b3
- a2 c2b1b2
- a1 c1,b1;c2,b2;c2,b3
- a2 c2,b1;c5,b2
- SELECT
- a,
- STRING_AGG('(' || c || ',' || b || ')', ' ; ')
- FROM
- tbl
- GROUP BY
- a;
- CREATE FUNCTION cut_semicolon(text) RETURNS text AS '
- BEGIN
- RETURN SUBSTRING($1 FROM 4);
- END;
- ' LANGUAGE 'plpgsql' IMMUTABLE;
- CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS '
- BEGIN
- RETURN $1 || '' ; '' || $2;
- END;
- ' LANGUAGE 'plpgsql' IMMUTABLE;
- CREATE AGGREGATE concat_semicolon(
- BASETYPE=text,
- SFUNC=concat_semicolon,
- STYPE=text,
- FINALFUNC=cut_semicolon,
- INITCOND=''
- );
- SELECT
- a,
- CONCAT_SEMICOLON('(' || c || ',' || b || ')')
- FROM
- tbl
- GROUP BY
- a;
- SELECT
- a,
- GROUP_CONCAT(CONCAT('(', c, ',', b, ')') SEPARATOR ' ; ')
- FROM
- tbl
- GROUP BY
- a;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement