Fill the column data with specific row value

• deepeshdhake

  SSChasing Mays

  Points: 648

  More actions

  August 27, 2012 at 8:39 pm

  #266674

  I have 2 tables: A and B

  This is the result obtained:

  PKCostCenterAmountAssignment Xfd1

  501002101250.00T0250076 100210

  501002101250.00T0250076 100210

  401002501250.00T0250076100250

  401003701250.00T0250076100370

  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

  Ten Centuries

  Points: 1317

  More actions

  August 27, 2012 at 8:54 pm

  #1530578

  please send DDL and elaborate your question.

• deepeshdhake

  SSChasing Mays

  Points: 648

  More actions

  August 27, 2012 at 8:59 pm

  #1530580

  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

  Ten Centuries

  Points: 1317

  More actions

  August 27, 2012 at 11:34 pm

  #1530595

  Hi UNION ALL does not give in build ordering functionality .

  so use in line view (Sub query) Like

• SELECT PKCostCenterAmountAssignment Xfd1 FROM

  (

  SELECT PKCostCenterAmountAssignment Xfd1 FROM a

  UNION ALL

  SELECT PKCostCenterAmountAssignment Xfd1 FROM a

  )temp ORDER BY CostCenter DES

Or use CTE ..

,WITH TEMP AS (

SELECT PKCostCenterAmountAssignment Xfd1 FROM a

UNION ALL

SELECT PKCostCenterAmountAssignment Xfd1 FROM a

)

SELECT PKCostCenterAmountAssignment Xfd1 FROM TEMP

ORDER BY CostCenter DES