t-sql grouping query

based on the following table

Name
---------
A
A
A
B
B
C
C
C

I want to add another column to this table called 'OnGoing' and the values should alternate for each group of names. There are only two values 'X' and 'Y'. So the table will look like

Name     OnGoing
----------------
A        X
A        X
A        X 
B        Y
B        Y
C        X
C        X 
C        X

how to write such a query that can alterna开发者_JAVA百科te the values for each group of names.

How about something like

DECLARE @Table TABLE(
        Name VARCHAR(10)
)

INSERT INTO @Table SELECT 'A'
INSERT INTO @Table SELECT 'A' 
INSERT INTO @Table SELECT 'A' 
INSERT INTO @Table SELECT 'B' 
INSERT INTO @Table SELECT 'B' 
INSERT INTO @Table SELECT 'C' 
INSERT INTO @Table SELECT 'C' 
INSERT INTO @Table SELECT 'C' 

SELECT  *,
        CASE 
            WHEN RowNum % 2 = 0 
                THEN 'Y'
            ELSE 'X'
        END
FROM    @Table t INNER JOIN
        (
            SELECT  Name,
                    ROW_NUMBER() OVER (ORDER BY Name) RowNum
            FROM    @Table
            GROUP BY Name
        ) sub ON t.Name = sub.Name

Alter your table to include the new column:

ALTER TABLE YourTable ADD
    OnGoing char(1) NULL
GO

and then try this:

DECLARE @YourTable table (Name char(1), OnGoing char(1))
INSERT @YourTable Values ('A',NULL)
INSERT @YourTable Values ('A',NULL)
INSERT @YourTable Values ('A',NULL)
INSERT @YourTable Values ('B',NULL)
INSERT @YourTable Values ('B',NULL)
INSERT @YourTable Values ('C',NULL)
INSERT @YourTable Values ('C',NULL)
INSERT @YourTable Values ('C',NULL)


;WITH TableRows AS
(
 SELECT
     *, ROW_NUMBER() OVER(ORDER BY Name) AS RowID
     FROM (SELECT DISTINCT
               *
               FROM @YourTable
          ) dt
)
UPDATE y
    SET OnGoing=CASE
                   WHEN r.RowID % 2 = 1 THEN 'X'
                   ELSE 'Y'
                END
    FROM @YourTable           y
        INNER JOIN TableRows  r ON y.Name=r.Name

SELECT * FROM @YourTable

OUTPUT:

Name OnGoing
---- -------
A    X
A    X
A    X
B    Y
B    Y
C    X
C    X
C    X

(8 row(s) affected)

This will do the trick:

select
    t1.Name,
    sub1.OnGoing 
from
    TableOne t1
    INNER JOIN 
(select
    Name,
    CASE RANK() over (order by Name) % 2
    WHEN 0 THEN 'Y'
    WHEN 1 THEN 'X'
    END as OnGoing
from 
    TableOne 
group by Name) sub1 ON sub1.Name = t1.Name

This is the actual output from my test:

Name OnGoing
---- -------
A    X
A    X
A    X
B    Y
B    Y
C    X
C    X
C    X