Help in writing SQL

General

Help in writing SQL

Hi,

I have a table like below

zone Cities

East Chennai

East bangalore

West madurai

East virigina

South Africa

North America

south Austria

west russia

My output should be like this (concatenation of second column values when Zones are matching). Please help

Zone Cities

East chennai-bangalore-virigina

west madurai-russia

north America

south africa-austria

Thanks

Rajesh

Tags (1)
3 REPLIES
Enthusiast

Re: Help in writing SQL

Which TD version???

It looks like udfconcat is left for me :)....kidding

In the below query, you can use regexp_replace  to replace (")  by ' ' and comma (,) by -

select zone,tdstats.udfconcat(cities) from your_table group by 1;

Re: Help in writing SQL

Thank You...will try and get back to you

Enthusiast

Re: Help in writing SQL

Hi Rajesh,

I have created the above scenario and made a recursive query to achieve the above.

CREATE MULTISET VOLATILE TABLE VT_ZONE_CITIES
(
"ZONE" VARCHAR(10)
, CITIES VARCHAR(10)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_ZONE_CITIES VALUES ('East','Chennai');
INSERT INTO VT_ZONE_CITIES VALUES ('East','bangalore');
INSERT INTO VT_ZONE_CITIES VALUES ('West','madurai');
INSERT INTO VT_ZONE_CITIES VALUES ('East','virigina');
INSERT INTO VT_ZONE_CITIES VALUES ('South','Africa');
INSERT INTO VT_ZONE_CITIES VALUES ('North','America');
INSERT INTO VT_ZONE_CITIES VALUES ('south','Austria');
INSERT INTO VT_ZONE_CITIES VALUES ('west','russia');

CREATE MULTISET VOLATILE TABLE VT_ZONE_CITIES_SEQ AS
(
SELECT
"ZONE"
, CITIES
, ROW_NUMBER() OVER (PARTITION BY "ZONE" ORDER BY CITIES) AS SEQUENCE
FROM
VT_ZONE_CITIES
)
WITH DATA
ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE VT_YOUR_OUTPUT
(
"ZONE" VARCHAR(10)
, CITIES VARCHAR(5000)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_YOUR_OUTPUT
WITH RECURSIVE ZONE_CITIES
(
"ZONE"
, CITIES
, SEQUENCE
) AS
(
SELECT
"ZONE"
, CAST(CITIES AS VARCHAR(5000)) AS CITIES
, SEQUENCE
FROM
VT_ZONE_CITIES_SEQ
WHERE
SEQUENCE = 1
UNION ALL
SELECT
B."ZONE"
, A.CITIES || '-' || B.CITIES
, B.SEQUENCE
FROM
ZONE_CITIES A
INNER JOIN
VT_ZONE_CITIES_SEQ B
ON A."ZONE" = B."ZONE"
AND A.SEQUENCE + 1 = B.SEQUENCE
)
SELECT
A."ZONE"
, A.CITIES
FROM
ZONE_CITIES A
INNER JOIN
(
SELECT
"ZONE"
, MAX(SEQUENCE) AS MAX_SEQUENCE
FROM
ZONE_CITIES
GROUP BY 1
) B
ON A."ZONE" = B."ZONE"
AND A.SEQUENCE = B.MAX_SEQUENCE;

SELECT *
FROM VT_YOUR_OUTPUT;