SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fill the column data with specific row value


Fill the column data with specific row value

Author
Message
deepeshdhake
deepeshdhake
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 188
I have 2 tables: A and B

This is the result obtained:

PK CostCenter Amount Assignment Xfd1

50 100210 1250.00 T0250076 100210

50 100210 1250.00 T0250076 100210

40 100250 1250.00 T0250076 100250

40 100370 1250.00 T0250076 100370

Now here is my question:

In the Xfd1 column first 2 values are "100210" but there I want "100250 and 100370" respectively. This result is obtained by doing union all operation on tables. From 1st table I got 1st 2 records and then from 2nd table I got remaining rows. And then I did union all.
sandeep rawat
sandeep rawat
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 376
please send DDL and elaborate your question.
deepeshdhake
deepeshdhake
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 188
Sorry, my mistake, A and B are not the tables, just ignore that sentence.

There are many tables which are joined to build the 2 result set and then these 2 result set are combined using union all to get the above result.

There is no ddl stmt here. Tables are already created.
sandeep rawat
sandeep rawat
Right there with Babe
Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

Group: General Forum Members
Points: 793 Visits: 376
Hi UNION ALL does not give in build ordering functionality .

so use in line view (Sub query) Like

  • SELECT PK CostCenter Amount Assignment Xfd1 FROM
    (

    SELECT PK CostCenter Amount Assignment Xfd1 FROM a

    UNION ALL

    SELECT PK CostCenter Amount Assignment Xfd1 FROM a
    )temp ORDER BY CostCenter DES


  • Or use CTE ..


    ,WITH TEMP AS (
    SELECT PK CostCenter Amount Assignment Xfd1 FROM a

    UNION ALL

    SELECT PK CostCenter Amount Assignment Xfd1 FROM a
    )
    SELECT PK CostCenter Amount Assignment Xfd1 FROM TEMP
    ORDER BY CostCenter DES
    deepeshdhake
    deepeshdhake
    SSC-Enthusiastic
    SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

    Group: General Forum Members
    Points: 130 Visits: 188
    Let me clarify my question.

    PK CostCenter Amount Assignment Xfd1

    50 100210 1250.00 T0250076 100210

    50 100210 1250.00 T0250076 100210

    40 100250 1250.00 T0250076 100250

    40 100370 1250.00 T0250076 100370

    I want "100250" and "100370" (which is in CostCenter column in 3rd and 4th row) to be filled in place of 100210( which is in Xfd1 column in 1st and 2nd row)
    Lynn Pettis
    Lynn Pettis
    SSC Guru
    SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

    Group: General Forum Members
    Points: 92975 Visits: 38955
    Sorry, but my crystal ball is in the shop still. You haven't provided nearly enough information to really help you as we can't see from here what you see there. You have access to your tables, data, and understand what you are trying to accomplish even though you can't figure out how to get from A to B.

    You need to provide us with the information we need to help you. Start by reading the first article I reference below in my signature block. It will walk you through the steps to post the necessary information for us to help you.

    Please understand, that when we ask for sample data, we really don't want sensitive company data, just something you make up that is representatitve of the problem domain and that you can map back to your actual data.

    Cool
    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    sandeep rawat
    sandeep rawat
    Right there with Babe
    Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)Right there with Babe (793 reputation)

    Group: General Forum Members
    Points: 793 Visits: 376
    question is still not clear
    Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

    Group: General Forum Members
    Points: 50449 Visits: 21152
    sandeep rawat (8/28/2012)
    question is still not clear


    +1 - no idea what's going on here!


    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    Lynn Pettis
    Lynn Pettis
    SSC Guru
    SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)SSC Guru (92K reputation)

    Group: General Forum Members
    Points: 92975 Visits: 38955
    Phil Parkin (8/28/2012)
    sandeep rawat (8/28/2012)
    question is still not clear


    +1 - no idea what's going on here!


    I think the Hudson is clearer than this question.

    Cool
    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search