CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
stackoverflowuser
stackoverflowuser

Reputation: 22380

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 alternate the values for each group of names.

Upvotes: 1

Views: 98

Answers (3)

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59463

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

Upvotes: 0

KM.
KM.

Reputation: 103587

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)

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166396

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

Upvotes: 5

Related Questions