Post reply

Inserting a column into table

  • October 28, 2008 at 6:48 am

    #195084

    I have a table in which I want to add an column. This column is sort of a flag/time indicator..

    So, for every distinct pair of columns A,B,C..I want to add an extra column D which has four values (1,2,3,4)

    Sample:

    Original Table:

    A1B1C1

    A2B2C2

    New Table

    A1B1C11

    A1B1C12

    A1B1C13

    A1B1C14

    A2B2C21

    A2B2C22

    A2B2C23

    A2B2C24

    I am looking for ways to do this task.BTW, I have lots of records

    Thanks

  • October 28, 2008 at 6:55 am

    #890578

    Try this

    SELECT A, B, C, Row_number() OVER (Partition by A, B, C Order by A,B,C) AS Flag

    FROM

    (SELECT A,B,C FROM OriginalTable

    union all

    SELECT A,B,C FROM OriginalTable

    union all

    SELECT A,B,C FROM OriginalTable

    union all

    SELECT A,B,C FROM OriginalTable) Duplicated

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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