CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
SoConfused
SoConfused

Reputation: 1989

SQL - Need Multiple Rows for Duplicates

SELECT COLUMN_A, COLUMN_B, COLUMN_C
FROM MY_TABLE
WHERE SOMETHING IN ('10', '20', '30', '40', '50', '60', '10');

Since value '10' is a duplicate, I only get 6 rows of data back, instead of the desired 7 rows of data. I need results back for every value in ('10', '20', '30', '40', '50', '60', '10').

How can I get 2 rows of data back for value '10' instead of just 1 row? Although it may not seem practical, this is exactly what I need done. Any assistance is much appreciated.

Upvotes: 0

Views: 47

Answers (4)

Manoj Mathew
Manoj Mathew

Reputation: 41

You can use join for achieving this. First split the comma separated values in IN CLAUSE into table form and use join with your table.

Split function you can use MSDN https://blogs.msdn.microsoft.com/amitjet/2009/12/11/convert-comma-separated-string-to-table-4-different-approaches/ Sample code below: I used the CTE approach to split

DECLARE @PARAMS VARCHAR(50)='10, 20, 30, 40, 50, 60, 10'



;WITH STRCTE(START, STOP) AS
(

      SELECT  1, CHARINDEX(',' , @PARAMS )
      UNION ALL
      SELECT  STOP + 1, CHARINDEX(',' ,@PARAMS  , STOP + 1)
      FROM STRCTE
      WHERE STOP > 0
    )
    SELECT SUBSTRING(@PARAMS , START, CASE WHEN STOP > 0 THEN STOP-START ELSE 4000 END) AS STRINGVALUE INTO #TEMP FROM STRCTE

    SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE INNER JOIN #TEMP ON SOMETHING = STRINGVALUE

Upvotes: 0

Ctznkane525
Ctznkane525

Reputation: 7465

You could do something like this.

The string gets converted to a nested table query and then you join against that.

SELECT *
FROM
(
select
  trim(regexp_substr(t.MESSAGES, '[^,]+', 1, levels.column_value))  as SPLIT_MESSAGE
from 
  (SELECT '10, 20, 30, 40, 50, 60, 10' MESSAGES FROM DUAL) t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.MESSAGES, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
) A
    INNER JOIN MY_TABLE ON MY_TABLE.SOMETHING=A.SPLIT_MESSAGE

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

You could use UNION ALL like:

SELECT COLUMN_A,
       COLUMN_B,
       COLUMN_C
       FROM MY_TABLE
       WHERE SOMETHING IN ('10',
                           ...
                           '60')
UNION ALL
SELECT COLUMN_A,
       COLUMN_B,
       COLUMN_C
       FROM MY_TABLE
       WHERE SOMETHING IN ('10');

Or you could create a temporary table with the filter values and join it:

CREATE GLOBAL TEMPORARY TABLE SOMETHINGS
AS
SELECT '10' SOMETHING
       FROM DUAL
UNION ALL
...
UNION ALL
SELECT '10' SOMETHING
       FROM DUAL;

SELECT COLUMN_A,
       COLUMN_B,
       COLUMN_C
       FROM MY_TABLE
            INNER JOIN SOMETHINGS
                       ON SOMETHINGS.SOMETHING = MY_TABLE.SOMETHING;

DROP TABLE SOMETHINGS;

Upvotes: 1

The Impaler
The Impaler

Reputation: 48769

You can use UNION ALL to consolidate multiple queries.

SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE WHERE SOMETHING = '10'
union all
SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE WHERE SOMETHING = '20'
union all
SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE WHERE SOMETHING = '30'
union all
SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE WHERE SOMETHING = '40'
union all
SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE WHERE SOMETHING = '50'
union all
SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE WHERE SOMETHING = '60'
union all
SELECT COLUMN_A, COLUMN_B, COLUMN_C FROM MY_TABLE WHERE SOMETHING = '10'

In any case, it's unclear to me why you need this. Besides, it's not very efficient in terms of database load and performance, even if you have an index on SOMETHING.

Upvotes: 1

Related Questions