IFS and INDEX/ MATCH EXCEL COUNTERPART IN SQL QUERY

Question

My table looks like this

NAME        BRAND    REFERENCE       COMMENTS   <-Expected output
-------------------------------------------------
Gu          Skirt    101128           Pants
Cci         Pants    101127           Pants
Cha         Skirt    paired           paired
Gu          Pants    101128           Skirts
Nel         Skirt    nonpaired        UNIQUE
Gir         Pants    101188           Skirt
Baud        Skirt    dropped          DROPPED
Le          Pants    paired           PAIRED 
Gir         Skirt    101188           101178 
Vis         Socks                     blanks
Cci         Skirts   101127           Skirts

I wonder what code to use to get the Comments result.

First reference in NUMBERS should be paired. If reference numbers match, return value should be the Brand counterpart.

If reference is in Character, they have to fall under if statements: IF character is Nonpaired return value should be unique, dropped for dropped and so on. If the reference is blanks no change.

Is this possible?

Thank you so much.

@Chels - Can you explain this with example First reference in NUMBERS should be paired.If reference numbers match, return value should be the Brand counterpart. — Pரதீப்
– Pரதீப், Commented Oct 11, 2016 at 3:51
@Chels I have added the below answer as per your req... Try running it and see if it works... — Teja
– Teja, Commented Oct 11, 2016 at 4:12

Edward Comeau · Accepted Answer · 2016-10-11 11:14:28Z

1

You can use common table expressions to break down the problem which can help with maintaining the code.

I didn't use lag/lead because you only have two rows in the pair, so numbering the rows and joining the table to itself felt quicker and easier to follow.

Here's the code I've used to answer and test your question;

create table #source
(
    [NAME] varchar(200),
    [BRAND] varchar(200),
    [REFERENCE] varchar(200)
);

insert into #source values
    ('Gu','Skirt','101128'),
    ('Cci','Pants','101127'),
    ('Cha','Skirt','paired'),
    ('Gu','Pants','101128'),
    ('Nel','Skirt','nonpaired'),
    ('Gir','Pants','101188'),
    ('Baud','Skirt','dropped'),
    ('Le','Pants','paired'),
    ('Gir','Skirt','101188'),
    ('Vis','Socks',''),
    ('Cci','Skirts','101127'),
    ('Le','Socks','101188'),
    ('Uno','Socks','101101');

select * from #source;

with cteNumericRef as
(
    select [NAME],[BRAND],[REFERENCE]
    from #source
    where ISNUMERIC([REFERENCE]) = 1
)

, cteCheckRow as
(
    select [REFERENCE],
        'CHECK' as [COMMENT]
    from cteNumericRef
    group by [REFERENCE]
    having count(*) <> 2
)

, ctePairedRow as
(
    select
          num_ref.[NAME]
        , num_ref.[BRAND]
        , num_ref.[REFERENCE]
        , row_number() over (partition by num_ref.[REFERENCE] order by num_ref.[NAME]) as [Pair_Num]

    from cteNumericRef num_ref

    left join cteCheckRow check_row
        on check_row.[REFERENCE] = num_ref.[REFERENCE]

    where check_row.[REFERENCE] is null
)

, cteTextRow as
(
    select [NAME],[BRAND],[REFERENCE],
        case [REFERENCE]
            when 'paired' then 'PAIRED'
            when 'nonpaired' then 'UNIQUE'
            when 'dropped' then 'DROPPED'
            when '' then ''
        else 'CHECK' end as [COMMENT]
    from #source
    where ISNUMERIC([REFERENCE]) <> 1
)

select
    left_row.[NAME]
    , left_row.[BRAND]
    , left_row.[REFERENCE]
    , right_row.[BRAND] as [COMMENTS]
from ctePairedRow left_row
inner join ctePairedRow right_row
    on left_row.[REFERENCE] = right_row.[REFERENCE]
    and left_row.[Pair_Num] <> right_row.[Pair_Num]

union all

select
    num_ref.[NAME]
    , num_ref.[BRAND]
    , num_ref.[REFERENCE]
    , check_row.[COMMENT]
from cteNumericRef num_ref
inner join cteCheckRow check_row
    on check_row.[REFERENCE] = num_ref.[REFERENCE]

union all

select
      [NAME]
    , [BRAND]
    , [REFERENCE]
    , [COMMENT]
from cteTextRow;


drop table #source

answered Oct 11, 2016 at 11:14

Edward Comeau

4,1742 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Chels Over a year ago

Hi, i remodified some of it. Because i find error naming. naming the refences

Chels Over a year ago

can i ask additional changes? IT WORKED perfectly well for me. I had typo with the first run. LOL however i need to add partial lookup value for UID, UNIQUE ITEM ID. Will you please assist?

Edward Comeau Over a year ago

Is this an additional table you wish to join the whole result set too? If so you can wrap the final set of unions as a new cte, then join the result of that cte once to your lookup table with a left join if you don't expect results for every row. Can you expand on what you're asking?

Chels Over a year ago

@ edward, im sorry can you guide me? this is like my first time in sql :((( can you please assist in my other question. Big big thanks

Chels Over a year ago

Hi Edward. I already vote up. But it says, those with reputation of 15 below are countted but cannot change publicly displayed votes.

|

Teja · Accepted Answer · 2016-10-11 04:10:49Z

1

SELECT Name,
       Brand,
       Reference,
       CASE WHEN Reference = 'Paired' THEN 'Paired' 
            WHEN Reference = 'nonpaired' THEN 'Unique'
            WHEN Reference = 'dropped' THEN 'DROPPED'
            WHEN Reference = ' ' THEN 'blanks'
            WHEN Reference = Next_Ref AND rownum = 1 THEN next_brand
            WHEN Reference = Prev_Ref AND rownum = 2 THEN prev_brand
        END AS Comments
  FROM  
     (
       SELECT Name,
              Brand,
              Reference,
              LAG( Reference, 1 )OVER PARTITION BY ( Reference ORDER BY Brand ) AS Prev_Ref,
              LEAD( Reference, 1 )OVER PARTITION BY ( Reference ORDER BY Brand ) AS Next_Ref,
              LAG( Brand, 1 ) OVER PARTITION BY ( Reference ORDER BY Brand ) AS Prev_Brand,
              LEAD( Brand, 1 ) OVER PARTITION BY ( Reference ORDER BY Brand ) AS Next_Brand,
              ROW_NUMBER( ) OVER PARTITION BY ( Reference ORDER BY Brand ) AS rownum
         FROM Data
     );

answered Oct 11, 2016 at 4:10

Teja

13.7k38 gold badges103 silver badges164 bronze badges

3 Comments

Chels Over a year ago

thanks @teja i will try this now if it works for me! much thanks

Chels Over a year ago

can youplease assist there is an error in lag and lead part of the formula

Chels Over a year ago

hi the first case when helped.

Collectives™ on Stack Overflow

IFS and INDEX/ MATCH EXCEL COUNTERPART IN SQL QUERY

2 Answers 2

9 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related