Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fill the column data with specific row value Expand / Collapse
Author
Message
Posted Monday, August 27, 2012 8:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:47 PM
Points: 32, Visits: 166
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.
Post #1350665
Posted Monday, August 27, 2012 8:54 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 713, Visits: 338
please send DDL and elaborate your question.
Post #1350670
Posted Monday, August 27, 2012 8:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 12:47 PM
Points: 32, Visits: 166
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.
Post #1350672
Posted Monday, August 27, 2012 11:34 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 5:10 AM
Points: 713, Visits: 338
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
    Post #1350690
    Posted Tuesday, August 28, 2012 10:14 AM
    SSC Rookie

    SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

    Group: General Forum Members
    Last Login: Thursday, April 10, 2014 12:47 PM
    Points: 32, Visits: 166
    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)
    Post #1351082
    Posted Tuesday, August 28, 2012 10:25 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Today @ 12:53 PM
    Points: 22,492, Visits: 30,189
    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.



    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)
    Post #1351096
    Posted Tuesday, August 28, 2012 10:26 AM
    Say Hey Kid

    Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

    Group: General Forum Members
    Last Login: Today @ 5:10 AM
    Points: 713, Visits: 338
    question is still not clear
    Post #1351097
    Posted Tuesday, August 28, 2012 10:45 AM


    SSCarpal Tunnel

    SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

    Group: General Forum Members
    Last Login: Yesterday @ 4:41 AM
    Points: 4,828, Visits: 11,180
    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.

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1351114
    Posted Tuesday, August 28, 2012 10:49 AM


    SSC-Insane

    SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

    Group: General Forum Members
    Last Login: Today @ 12:53 PM
    Points: 22,492, Visits: 30,189
    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.



    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)
    Post #1351118
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse