Results 1 to 4 of 4

Thread: Split a column

  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: Split a column

    Hi everybody

    Does any body know how to split a field in a table into two fields

    eg
    usermaster(table)
    userid(field)

    usermaster has 40 users with user id 1 to 40
    i want to get data as

    userid userid
    1 21
    2 22
    3 23
    . .
    . .
    . .
    20 40

    Thanks you very much

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    If you post the DDL and sample data, that would help us to figure it out...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2004
    Posts
    34

    sql query

    usermaster (table)
    userid username
    1 a
    2 b
    3 c
    4 d
    . .
    . .
    40 aa

    i need a select query result in this format
    userid username userid username
    1 a 21 l
    2 b 22 m
    3 c 23 n
    . . . .
    . .
    20 k 40 pp

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I hope this aint homework...

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int, Col2 char(2))
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 1,  'A'  UNION ALL
    SELECT 2,  'B'  UNION ALL
    SELECT 3,  'C'  UNION ALL
    SELECT 4,  'D'  UNION ALL
    SELECT 5,  'E'  UNION ALL
    SELECT 6,  'F'  UNION ALL
    SELECT 7,  'G'  UNION ALL
    SELECT 8,  'H'  UNION ALL
    SELECT 9,  'I'  UNION ALL
    SELECT 10, 'J'  UNION ALL
    SELECT 11, 'K'  UNION ALL
    SELECT 12, 'L'  UNION ALL
    SELECT 13, 'M'  UNION ALL
    SELECT 14, 'N'  UNION ALL
    SELECT 15, 'O'  UNION ALL
    SELECT 16, 'P'  UNION ALL
    SELECT 17, 'Q'  UNION ALL
    SELECT 18, 'R'  UNION ALL
    SELECT 19, 'S'  UNION ALL
    SELECT 20, 'T'  UNION ALL
    SELECT 21, 'U'  UNION ALL
    SELECT 22, 'V'  UNION ALL
    SELECT 23, 'X'  UNION ALL
    SELECT 24, 'Y'  UNION ALL
    SELECT 25, 'Z'  UNION ALL
    SELECT 26, 'AA' UNION ALL
    SELECT 27, 'AB' UNION ALL
    SELECT 28, 'AC' UNION ALL
    SELECT 29, 'AD' UNION ALL
    SELECT 30, 'AE' UNION ALL
    SELECT 31, 'AF' UNION ALL
    SELECT 32, 'AG' UNION ALL
    SELECT 33, 'AH' UNION ALL
    SELECT 34, 'AI' UNION ALL
    SELECT 35, 'AJ' UNION ALL
    SELECT 36, 'AK' UNION ALL
    SELECT 37, 'AL' UNION ALL
    SELECT 38, 'AM' UNION ALL
    SELECT 39, 'AN' UNION ALL
    SELECT 40, 'AO'
    GO
    
        SELECT Col1, Col2, Col3, Col4
          FROM myTable99 
    INNER JOIN (SELECT Col1 AS Col3, Col2 AS Col4
    	      FROM myTable99
    	     WHERE Col1 > 20 AND Col1 < 41) AS XXX
    	ON Col1 = Col3 - 20
         WHERE Col1 < 21
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •