CODEWITHSUNDEEP
sqlsql-servert-sql
Passiontolearn
Passiontolearn

Reputation: 103

SQL , self join

I have a set of data

name
AASADF2
AASADF3
ADSFFD2
ADSFFD3
AAWFWEF
SFASFSF
ADAQWEW
ASDAWFA
FSDGFRG
AFWEFR2
AFWEFR3

I wanted to retrieve data with name ending 2 or 3 also first 6 character should match

i.e
AASADF2
AASADF3
ADSFFD2
ADSFFD3
AFWEFR2
AFWEFR3

I was able to display the data vertically using self join

AASADF2     AASADF3
ADSFFD2     ADSFFD3
AFWEFR2     AFWEFR3

But I wanted that in horizontal format

AASADF2
AASADF3
ADSFFD2
ADSFFD3
AFWEFR2
AFWEFR3

Do we need to create temp table for this to acheive this format Any thoughts?

Upvotes: 0

Views: 95

Answers (4)

Passiontolearn
Passiontolearn

Reputation: 103

I was able to retrieve partial results, If we check count > 1 which will show the results which I am looking for , I used the below query

;with cte as (select t1.name from t_name t1

where substring(t1.name, 8, 1) in ( select substring(t2.name, 8, 1) from t_name t2 where substring(t2.name, 8, 1)='2' or substring(t2.name, 8, 1)='3' ) )

B2018BT2 1 B2018BU2 1 B2018BV2 1 B2018BW2 1 B2018BX2 1 B2018BY2 1 B2018BZ2 1 B2020AA2 2 B2020AA3 2 B2020AB2 2 B2020AB3 2 B2020AC2 2 B2020AC3 2

Can someone suggest how to do a filer on count>1?

Upvotes: 0

Cato
Cato

Reputation: 3701

  SELECT distinct t1.field FROM TABLE t1 JOIN TABLE t2 
                   ON t1.field != t2.field 
                      AND RIGHT(t1.field,1 ) IN ('2', '3')
                      AND LEFT(t1.field, 6) = LEFT(t2.field,6);

--*********************************************

here is a full example for you

DECLARE @table TABLE
(
  field nvarchar(10)
)

insert @table (field) values ('AASADF2'),
                        ('AASADF3'),
                        ('ADSFFD2'),
                        ('ADSFFD3'),
                        ('AAWFWEF'),
                        ('SFASFSF'),
                        ('ADAQWEW'),
                        ('ASDAWFA'),
                        ('FSDGFRG'),
                        ('AFWEFR2'),
                        ('AFWEFR3');



SELECT distinct t1.field FROM @table t1 JOIN @table t2 
                   ON t1.field != t2.field 
                      AND RIGHT(t1.field,1 ) IN ('2', '3')
                      AND LEFT(t1.field, 6) = LEFT(t2.field,6)

output

field
----------
AASADF2
AASADF3
ADSFFD2
ADSFFD3
AFWEFR2
AFWEFR3

(6 row(s) affected)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One method uses window functions:

select t.*
from (select t.*, count(*) over (partition by left(t.field, 6)) as cnt
      from t
     ) t
where cnt > 1 and field like '%[23]';

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Is this what you want :

select t.*
from table t
where right(nm, 1) in ('2', '3') and
      exists (select 1 from table t1 where left(t1.nm, 6) = left(t.nm, 6))

Upvotes: 1

Related Questions