none
Query needed

    Question

  • declare @temp table (id int, jobrole varchar(10))
    insert into @temp(id,jobrole)
    select 1,'admin' union
    select 1,'user' union
    select 1,'corporate' union
    select 2,'developer' union
    select 2,'analyst' union
    select 3,'analyst' union
    select 3,'admin'

    I want a query - It has to return 1, if their role has 'admin' or 'corporate'.  Has to 0 , otherwise.

    I don't want to return multiple roles. Instead, the result should be,

    ID Answer
    1   1
    2   0
    3   1

    Thanks.


    NSG12


    • Edited by NSG12 Tuesday, July 16, 2013 7:51 PM
    Tuesday, July 16, 2013 7:50 PM

Answers

  • This seems like a homework question.

    There are many ways to do this.  This is one:

    declare @temp table (id int, jobrole varchar(10))
    insert into @temp(id,jobrole)
    select 1,'admin' union
    select 1,'user' union
    select 1,'corporate' union
    select 2,'developer' union
    select 2,'analyst' union
    select 3,'analyst' union
    select 3,'admin' 
    select
    	t1.id,
    	Answer = CASE WHEN CHARINDEX('admin',substring((SELECT ( ', ' + jobrole )
                               FROM @temp t2
                               WHERE t1.ID = t2.ID
                               ORDER BY 
                                  ID,
                                  jobrole
                               FOR XML PATH( '' )
                              ), 3, 1000 )) > 0
    						  OR CHARINDEX('corporate',substring((SELECT ( ', ' + jobrole )
                               FROM @temp t2
                               WHERE t1.ID = t2.ID
                               ORDER BY 
                                  ID,
                                  jobrole
                               FOR XML PATH( '' )
                              ), 3, 1000 )) > 0 THEN 1 ELSE 0 END
    FROM @temp t1
    GROUP BY t1.id

    Tuesday, July 16, 2013 8:05 PM
    Moderator

All replies

  • Hi NSG12,

    Is this what you want?

    select distinct case when jobrole = 'admin' then jobrole
            when jobrole = 'corporate' then jobrole
            else 'other' end as JobRole
        , case when jobrole = 'admin' then 1
            when jobrole = 'corporate' then 1
            else 0 end as Flag
    from @temp

    Thanks,


    Puja


    • Edited by PShah109 Tuesday, July 16, 2013 8:17 PM space
    Tuesday, July 16, 2013 8:00 PM
  • This seems like a homework question.

    There are many ways to do this.  This is one:

    declare @temp table (id int, jobrole varchar(10))
    insert into @temp(id,jobrole)
    select 1,'admin' union
    select 1,'user' union
    select 1,'corporate' union
    select 2,'developer' union
    select 2,'analyst' union
    select 3,'analyst' union
    select 3,'admin' 
    select
    	t1.id,
    	Answer = CASE WHEN CHARINDEX('admin',substring((SELECT ( ', ' + jobrole )
                               FROM @temp t2
                               WHERE t1.ID = t2.ID
                               ORDER BY 
                                  ID,
                                  jobrole
                               FOR XML PATH( '' )
                              ), 3, 1000 )) > 0
    						  OR CHARINDEX('corporate',substring((SELECT ( ', ' + jobrole )
                               FROM @temp t2
                               WHERE t1.ID = t2.ID
                               ORDER BY 
                                  ID,
                                  jobrole
                               FOR XML PATH( '' )
                              ), 3, 1000 )) > 0 THEN 1 ELSE 0 END
    FROM @temp t1
    GROUP BY t1.id

    Tuesday, July 16, 2013 8:05 PM
    Moderator
  • declare @temp table (id int, jobrole varchar(10))
    insert into @temp(id,jobrole)
    select 1,'admin' union
    select 1,'user' union
    select 1,'corporate' union
    select 2,'developer' union
    select 2,'analyst' union
    select 3,'analyst' union
    select 3,'admin' 
    --SELECT * FROM @TEMP
    SELECT ID,MAX(CASE
                    WHEN jobrole = 'admin'  OR jobrole = 'corporate' THEN 1
                    ELSE 0
                  END) AS Answer
    FROM   @temp
    GROUP  BY id 
    /*
    ID	Answer
    1	1
    2	0
    3	1
    */

    Tuesday, July 16, 2013 8:12 PM
    Moderator
  • Not exactly.  If the ID has 'admin','corporate' then it should return 1 only once. 

    ID Answer
    1   1---here it returned 1 because, the ID has 'admin','corporate'
    2   0---here it returned 0 because, the ID did not have 'admin'or 'corporate'
    3   1---here it returned 1 because, the ID had 'admin'


    NSG12

    Tuesday, July 16, 2013 8:13 PM
  • Oh super.  This query helped.  Thank you all for your time.

    NSG12

    Tuesday, July 16, 2013 8:16 PM