Professional Web Applications Themes

STUCK, help?? - Microsoft SQL / MS SQL Server

CREATE TABLE dbo.Table1 ( INS nvarchar(50) NULL, INS_AMT money NULL, PAT_AMT money NULL ) ON [PRIMARY] GO Insert Into dbo.Table1( INS, INS_AMT, PAT_AMT) Values ('MCARE', 80, 20) Insert Into dbo.Table1( INS, INS_AMT, PAT_AMT) Values ('BCBS', 90,10) Insert Into dbo.Table1( INS, INS_AMT, PAT_AMT) Values ('SELF', 0, 100) What I'm trying to do is combine the two AMT fields into one new calculated column, AND would like to create a new field for the INS field that uses the INS field only for the INS_AMT value, and place a new text value 'PAT' for PAT_AMT values, The final result should look something ...

  1. #1

    Default STUCK, help??

    CREATE TABLE dbo.Table1
    (
    INS nvarchar(50) NULL,
    INS_AMT money NULL,
    PAT_AMT money NULL
    ) ON [PRIMARY]
    GO
    Insert Into dbo.Table1( INS, INS_AMT, PAT_AMT)
    Values ('MCARE', 80, 20)
    Insert Into dbo.Table1( INS, INS_AMT, PAT_AMT)
    Values ('BCBS', 90,10)
    Insert Into dbo.Table1( INS, INS_AMT, PAT_AMT)
    Values ('SELF', 0, 100)

    What I'm trying to do is combine the two AMT fields into
    one new calculated column, AND would like to create a new
    field for the INS field that uses the INS field only for
    the INS_AMT value, and place a new text value 'PAT' for
    PAT_AMT values,

    The final result should look something like

    MCARE 80
    PAT 20
    BCBS 90
    PAT 10
    SELF 0
    PAT 100

    I'm totally stuck, any suggestions???

    THANKS!!!
    gv Guest

  2. #2

    Default Re: STUCK, help??

    The simplest way I can think of is:
    SELECT INS, INS_AMT = CONVERT(INT, INS_AMT) FROM Table1
    UNION
    SELECT 'PAT', PAT_AMT = CONVERT(INT, PAT_AMT) FROM Table1

    To get the items one below the other, you might have to add some ID column
    and order by that column.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "gv" <org> wrote in message
    news:06bd01c351a4$31891660$gbl... 


    SriSamp Guest

  3. #3

    Default Re: STUCK, help??

    If you use a subquery you can hide the order by columns.

    SELECT INS, AMT
    FROM
    (
    SELECT INS as Sort1, 1 as Sort2, INS, CAST(INS_AMT as int) as AMT
    FROM Table1
    UNION ALL
    SELECT INS as Sort1, 2 as Sort2, 'PAT', CAST(PAT_AMT as int) as AMT
    FROM Table1
    ) AS Subquery
    ORDER BY Sort1, Sort2

    "SriSamp" <co.in> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Anthony Guest

  4. #4

    Default Re: STUCK, help??

    THANKS 
    int) as AMT 
    int) as AMT [/ref]
    Table1 [/ref]
    add some ID column [/ref][/ref]
    into [/ref][/ref]
    new [/ref][/ref]
    for [/ref][/ref]
    for 
    >>
    >>[/ref]
    >
    >
    >.
    >[/ref]
    gv Guest

  5. #5

    Default Re: STUCK, help??

    THANKS! 
    add some ID column [/ref]
    new 
    >
    >
    >.
    >[/ref]
    gv Guest

Similar Threads

  1. CFC Query - stuck, stuck, stuck
    By Howard Perlman in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: July 7th, 02:56 PM
  2. Stuck again
    By Andy in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 13th, 09:41 AM
  3. I'm stuck
    By Mauricio Fernández in forum Ruby
    Replies: 1
    Last Post: October 9th, 05:33 PM
  4. [PHP] Am stuck
    By Raquel Rice in forum PHP Development
    Replies: 1
    Last Post: September 25th, 04:03 PM
  5. php.. Am I stuck??
    By No in forum PHP Development
    Replies: 1
    Last Post: August 20th, 07:23 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139