none
Get Alternate Values

    General discussion

  • I have data as below:

    Name          Gender

    A                   Male

    B                   Male

    C                  Female

    D                  Female

    I want to get output as below

    Name           Gender

    A                   Male

    c                   Female

    B                   Male

    D                  Female

    How to get this?


    Wednesday, June 19, 2013 5:12 AM

All replies

  • Try the below:

    Create Table T11(Name Varchar(50),Gender Varchar(50))
    Insert into T11 Values
    ('A','Male'),
    ('B','Male'),
    ('C','Female'),
    ('D','Female')
    
    ;With cte
    AS
    (
    	Select *, ROW_NUMBER()Over(PARTITION by Gender Order by Gender desc,Name asc) RID
    	From T11
    )Select NAme, Gender From cte Order by RID asc , Len(Gender) Asc
    
    Drop table T11


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.



    Wednesday, June 19, 2013 5:17 AM

  • Name           Gender

    A                   Male

    c                   Female

    B                   Male

    D                  Female



    TRY LIKE THIS:

    DECLARE @T TABLE(ID INT,NAME VARCHAR(10),SAL INT)
    INSERT INTO @T 
    SELECT 1 , 'AAA' , 1000
    UNION ALL
    SELECT 2 , 'BBB', 2000
    UNION ALL
    SELECT 3 , 'CCC' , 3000
    UNION ALL
    SELECT 4 , 'DDD' , 4000
    UNION ALL
    SELECT 5 , 'EEE' , 5000
    UNION ALL
    SELECT 6, 'FFFF' , 6000
    UNION ALL
    SELECT 7 , 'GGG' , 7000
    
    --SELECT * FROM @T
    SELECT ID,NAME,SAL FROM
    (
    SELECT *,CASE WHEN ID%2=1 THEN 0 ELSE SAL END AS SAL_DESC FROM @T
    )
    TEMP
    ORDER BY SAL_DESC 

    Thanks.


    bala krishna

    Wednesday, June 19, 2013 5:26 AM
  • With your sample data:

    CREATE TABLE TEST(NAME VARCHAR(50),GENDER VARCHAR(50))
    INSERT INTO TEST VALUES
    ('A','MALE'),
    ('B','MALE'),
    ('C','FEMALE'),
    ('D','FEMALE')
    
    SELECT NAME,GENDER FROM
    (
    SELECT ROW_NUMBER() OVER(ORDER BY NAME) AS RNUM,* FROM TEST
    ) TEMP
    ORDER BY CASE WHEN RNUM%2=1 THEN 0 ELSE RNUM END

    Thanks.


    bala krishna

    Wednesday, June 19, 2013 5:36 AM
  • What is expected if there is another row with 'E','F" ?

    some thing like this A,C.B,D,F,E? 


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, June 19, 2013 5:43 AM

  • some thing like this A,C.B,D,F,E? 



    @Sarat, As for my code result set will be based on EVEN or ODD Number

    So, output will be as following:

    A

    B

    F

    C

    D

    E

    Thanks.


    bala krishna

    Wednesday, June 19, 2013 5:54 AM