Hi All,
I have below values in a table t1 for a column:
col1
----
ab
bc
cd
ef
How can i group all column values into a single column value as below:
('ab','bc','cd','ef')
---------------------------------------------------
In SQL Server we have used below query:
SELECT test.col1 as col1,
test.col2 as col2 FROM
(
SELECT col1,
'(' + STUFF((SELECT ''',''' + A.col2
FROM
(select 1 as col1,col2 from dbo.t1 ) as A
WHERE A.col1=B.col1 FOR XML PATH('')),1,2,'')+ ''')' As col2
FROM
(select 1 as col1,col2 from dbo.t1 ) as B
GROUP BY col1
) test
output: ('ab','bc','cd','ef')
------------------------------------------------------
How can i do this using vertica queries and functions?
Can any one help on this,
Thanks in advance
How to group all values in a column into one column value
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: How to group all values in a column into one column valu
The Vertica-Extension-Packages has a group_concat function that you can try.
Checkout vertica.tips for more Vertica resources.
Re: How to group all values in a column into one column valu
Hello Verti,
Using Group concat functions is the easiest way to implement this.
Other then this, if you can't use this function and want pure SQL solution, there is an excellent workaround by jim here
http://www.vertica-forums.com/viewtopic.php?f=48&t=160
Hope this helps
Using Group concat functions is the easiest way to implement this.
Other then this, if you can't use this function and want pure SQL solution, there is an excellent workaround by jim here
http://www.vertica-forums.com/viewtopic.php?f=48&t=160
Hope this helps