CODEWITHSUNDEEP
sqloracle-database
dokgu
dokgu

Reputation: 6080

SQL Consolidate Column

I want to get all the strings found under a column and build a string combination based on them.

To illustrate:

Objects (o)

id  group_id   name        type                     
----------------------------------------------------
1   100        Computer    Computer - Standard
2   100        Telephone   Telephone - Modified
3   100        Table       Table - Standard - Deal

As you can see under type I'm looking for the words Standard and Modified (there are others but I only show 2 for simplicity) and want to build a string combining these words once and have the result as a column for each row.

I can get the keyword for each row using CASE and LIKE and assign that word as the value of the column like these:

id  group_id   name        type                     consolidated
-----------------------------------------------------------------------
1   100        Computer    Computer - Standard      Standard
2   100        Telephone   Telephone - Modified     Modified
3   100        Table       Table - Standard - Deal  Standard

But that's not really what I wanted. I just don't know how to join them like I want to.

id  group_id   name        type                     consolidated
-----------------------------------------------------------------------
1   100        Computer    Computer - Standard      Standard / Modified
2   100        Telephone   Telephone - Modified     Standard / Modified
3   100        Table       Table - Standard - Deal  Standard / Modified

Edit

Sorry I forgot to add my current query.

SELECT o.id, o.group_id, o.name, o.type,
CASE
  WHEN o.type LIKE '%Standard%' THEN 'Standard'
  WHEN o.type LIKE '%Modified%' THEN 'Modified'
  ELSE ''
END AS consolidated
FROM objects o
WHERE o.group_id = 100

Upvotes: 0

Views: 62

Answers (1)

shawnt00
shawnt00

Reputation: 17953

WITH q as (
    SELECT o.id, o.group_id, o.name, o.type,
    CASE
        WHEN o.type LIKE '%Standard%' THEN 'Standard'
        WHEN o.type LIKE '%Modified%' THEN 'Modified'
        ELSE ''
    END AS lbl
    FROM objects o
    WHERE o.group_id = 100
)
SELECT
    id, group_id, name, type,
    (
      SELECT LISTAGG(lbl, ' / ') WITHIN GROUP (ORDER BY ??)
      FROM (SELECT DISTINCT lbl FROM q) dc
    ) as consolidated
FROM q;

Upvotes: 1

Related Questions