CODEWITHSUNDEEP
sqlpostgresql
Wildan Muhlis
Wildan Muhlis

Reputation: 1613

Single text value which get unique value from all rows

Assume I have these rows:

ROW 1 apple,watermelon,pineapple

ROW 2 apple,pineapple,orange

ROW 3 apple,blue berry

I want to create single text value which get unique value from all rows.

How do we do it with query?

Expected Result:

apple,watermelon,pineapple,orange,blue berry

Upvotes: 1

Views: 156

Answers (3)

Bohemian
Bohemian

Reputation: 425258

Try this:

select array_agg(val) from (
  select distinct unnest(string_to_array(my_column, ',')) val from my_table) x

A breakdown of what's going on:

  • string_to_array() splits the string, using the specified delimiter, into a true array
  • unnest() turns an array into separate rows - one for each element
  • distinct removes duplicate rows
  • array_agg() joins all rows into a single CSV string (typically you would us a group by clause, but no need here as there's only one group)

Upvotes: 2

dArc
dArc

Reputation: 342

try this:

SELECT ARRAY(SELECT distinct    regexp_split_to_table(myTable.columns, E',') AS split_columns FROM myTable);

Upvotes: 0

SAM
SAM

Reputation: 835

You can use Following function to Split the row values by ',' 

CREATE FUNCTION [dbo].[ConvertToTable]
    (
      @delimiter char(1),
      @string nvarchar(MAX)
    )
RETURNS @Values TABLE ( VALUE NVARCHAR(MAX) )
AS BEGIN
    SET @string = @string + @delimiter ;

    WITH    Nbrs_3 ( n ) AS ( SELECT   1 UNION SELECT   0 ) ,
            Nbrs_2 ( n ) AS ( SELECT   1 FROM     Nbrs_3 n1 CROSS JOIN Nbrs_3 n2 ) ,
            Nbrs_1 ( n ) AS ( SELECT   1 FROM     Nbrs_2 n1 CROSS JOIN Nbrs_2 n2 ) ,
            Nbrs_0 ( n ) AS ( SELECT   1 FROM     Nbrs_1 n1 CROSS JOIN Nbrs_1 n2 ) ,
            Nbrs ( n ) AS ( SELECT   1 FROM     Nbrs_0 n1 CROSS JOIN Nbrs_0 n2 )
       INSERT @Values ( [VALUE] )
        SELECT  SUBSTRING(@string, n + 1, CHARINDEX(@delimiter, @string, n + 1) - n - 1)
        FROM    ( SELECT    0 AS 'n' UNION ALL
                  SELECT TOP ( LEN(@string) - 1 ) ROW_NUMBER() OVER ( ORDER BY n ) AS 'n' FROM      Nbrs
                ) x
        WHERE   SUBSTRING(@string, n, 1) = @delimiter
                OR n = 0

    RETURN 
   END

And Use following Code to Get the result..

DECLARE @unique_value NVARCHAR(MAX)

WITH cte AS (
SELECT 1 AS id, 'apple,watermelon,pineaple' AS String
UNION
SELECT 2 ,'apple,pineaple,orange'
UNION
SELECT 3 ,'apple,blue berry'

)

SELECT @unique_value= COALESCE(@unique_value+',','')+[VALUE] FROM [cte]
CROSS APPLY [dbo].[ConvertToTable](',',[String]) AS CTT
GROUP BY [VALUE]

SELECT @unique_value

UPDATE: I didn't notice this is about postgresql. i have gave the answer for MSSQL, If you could do the same in postgresql. this method would help you..

Upvotes: 0

Related Questions