Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    202

    Unanswered: sorting out data in a field

    Hi there,

    I got one prob and i need help to solve it.
    I got this one field in a table which contain a lot of datas and i need to sort it out into as many different data as a field for each.
    Example:

    ASP_Tin No
    ----------------

    ss24fg
    ss98jk
    tp98ij
    yh88ij
    yh67tr
    tp34ed
    fg98bv
    fg56sl
    ..........

    and i need to sort it out into like this

    ss tp yh fg
    ------------- ------------- ---------------- ---------------
    ss24fg tp98ij yh88ij fg98bv
    ss98jk tp34ed yh67tr fg56sl

    i need to do it in sql statement which is in stored procedure
    hope somebody can help me
    Thanx

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: sorting out data in a field

    Hi there,

    It would be beneficial if you would post some CREATE object statements and sample data in order for us to give you a quick and accurate solution.

    Anyway, here is a quick suggestion which you might want to adapt to suit your query needs :

    Assuming we have a table with the following columns ss, tp, yh and fg, you can do something like this,

    Code:
    update myTable
    set ss = case when left(ASP_Tin, 2) = 'ss' then ASP end,
    set tp = case when left(ASP_Tin, 2) = 'tp' then ASP end,
    set yh = case when left(ASP_Tin, 2) = 'yh' then ASP end,
    set fg = case when left(ASP_Tin, 2) = 'fg' then ASP end
    from someTable
    Shadow to Light

  3. #3
    Join Date
    Mar 2004
    Posts
    202

    Re: sorting out data in a field

    hi again,

    Actually i havent create anything yet coz i'm so sure how to do it.
    Well, i'm actually have this table already which has this field that these datas and they are all mix up. My duty is to write sql statement in stored procedure to separate all the datas out into designated field which are not created in a table or sumting like that.it just can be shown out in the Sql Query Analyser and not to update any table or create a new one.
    The source of table is as follow:

    Parking no Place Code Price
    ------------ ------------- -------
    123ghj765 g333 400
    765tre888 k898 300
    162yue283 g333 323
    987gtg890 g333 333
    323gtg567 h393 222
    563slw765 k898 989

    Basically the output at the SQL Query Analyser would be like this:

    Parking no g333 k898 h393
    ----------- ------ ------ ----
    123ghj765 400 0 0
    765tre888 0 300 0
    162yue283 323 0 0
    987gtg890 333 0 0
    323gtg567 0 0 222
    563slw765 0 989 0

    Is sumting like this.
    Help please.
    Thanx
    Last edited by michaelfg81; 04-19-04 at 20:46.

Posting Permissions

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