Query Help

  • Hello

    I have one requirement

    please help me

    create table #x1

    (ano int,

    dept varchar(30))

    insert into #x1 values(1,'A1,A3,A5,A7')

    insert into #x1 values(2,'A1,A2,A3,A4')

    insert into #x1 values(3,'A2,A5,A9,A10')

    insert into #x1 values(4,'A3,A10,A7')

    insert into #x1 values(5,'A6,A7')

    If i filter to A1, A5 and A6 then

    desired output will be

    ano

    1

    2

    3

    5

  • Tempting to just say:

    select ano from #x1 where ano <> 4

    order by ano

    but there must be some rule involving the 'dept' column in play here. What is the rule you need the SQL to apply?


    And then again, I might be wrong ...
    David Webb

  • are you sure there isnt anymore to your requirements? looks a bit too simple to me

    select ano from #x1

    where ano!=4

    order by ano

    ***The first step is always the hardest *******

  • David Webb-200187 (11/7/2012)


    Tempting to just say:

    select ano from #x1 where ano <> 4

    order by ano

    but there must be some rule involving the 'dept' column in play here. What is the rule you need the SQL to apply?

    sorry I missed this

    If i filter to A1, A5 and A6 then

    desired output will be

    ano

    1

    2

    3

    5

  • To Whoever Replyed,

    This is sample of data,actual table has 1 million row.

    Query should be something like

    select ano from #x1 where Dept in ('A1','A5','A6')

    And Output should be the "list of ano" where the comma seperated table data(Dept) match with comma seperated data of where clause.

  • OK,

    select ano from #x1 where dept like '%a1%' or dept like '%a5%' or dept like '%a6%'

    order by ano

    would do it, but I suspect there is more...

    Will these 3 conditions be entered as arguments to a stored proc, by any chance? If so, will they be entered as 3 separate arguments or as one string that will need to be separated?


    And then again, I might be wrong ...
    David Webb

  • You are rite David,

    Thanks for reply

    I have to replace one very complex table function join condition llisted as below,

    { JOIN TABLE A B C query part }

    Where XYZ= XYZ

    AND (@Dept IS NULL OR C.Dept IN (SELECT DEPT FROM dbo.f_BuildTable(',',@Dept)))

    Where F_BuildTable generate the value of @Dept from

    ('A1,A5,A7') to fit into in clause ('A1','A5','A7').

    meaning use is passing me as one string with comma 'A1,A5,A7' but I already have function to conver it into ('A1','A5','A7').

    Question is .. How would I do same for the table value(Dept col) which is like

    the 'A1,A2,A3,A4' and compair them and fit into the place of abovemention stmt.

  • OK, I'd do it slightly differently. Here's a link to the famous 8k splitter.

    http://www.sqlservercentral.com/articles/Tally+Table/72993/

    And I'd probably use it like:

    drop table #x1

    create table #x1

    (ano int,

    dept varchar(30))

    insert into #x1 values(1,'A1,A3,A5,A7')

    insert into #x1 values(2,'A1,A2,A3,A4')

    insert into #x1 values(3,'A2,A5,A9,A10')

    insert into #x1 values(4,'A3,A10,A7')

    insert into #x1 values(5,'A6,A7')

    declare @dept nvarchar(200)

    set @dept = 'A5,A7'

    select distinct(ano) from #x1 x

    cross apply [DelimitedSplit8K](x.dept,',') split

    where split.item in (select item from [DelimitedSplit8K](@dept,','))

    BTW, storing things you have to match to in delimited strings is hardly ever a good idea. If you can change the design to break these out into a separate table, this all becomes MUCH simpler.


    And then again, I might be wrong ...
    David Webb

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply