can anybody help me to solve this issue

• DURGESH-769360

  SSC Eights!

  Points: 968

  More actions

  March 10, 2009 at 4:08 am

  #71872

  hi all,

  i have a table as follows

  col1 col2 col3

  a aa z

  b ab z

  c ac z

  m am y

  n bn y

  and i need the final output as

  col1 col2

  a aa

  b ab

  c ac

  col3 z

  m am

  n bn

  col3 y

  can anybody help me to sort out this issue

  regards

  Durgesh J

• Mark Cowne

  One Orange Chip

  Points: 26865

  More actions

  March 10, 2009 at 4:33 am

  #956664

  This looks more like something that is handled better in your front end

  select coalesce(col1,'col3') as col1,col2

  from (select col1,col2,col3

  from mytable

  union

  select null,col3,col3

  from mytable)data(col1,col2,col3)

  order by col3 desc,case when col1 is not null then 0 else 1 end,col1

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• mahaboobvali.shaik

  Grasshopper

  Points: 21

  More actions

  June 20, 2012 at 1:57 am

  #1503257

  hi

• Dwain Camps

  SSC Guru

  Points: 86913

  More actions

  June 21, 2012 at 12:39 am

  #1503774

  An outstanding exercise in futility!

  DECLARE @t TABLE (col1 VARCHAR(2), col2 VARCHAR(2), col3 VARCHAR(2))

  INSERT INTO @t

  SELECT 'a','aa','z'

  UNION ALL SELECT 'b','ab','z'

  UNION ALL SELECT 'c','ac','z'

  UNION ALL SELECT 'm','am','y'

  UNION ALL SELECT 'n','bn','y'

  ;WITH t AS (

  SELECT col1, col2, col3

  ,n=ROW_NUMBER() OVER (PARTITION BY Col3 ORDER BY (SELECT NULL))

  ,m=MAX(Col1) OVER (PARTITION BY Col3)

  FROM @t),

  t2 AS (

  SELECT col1, col2, col3=MAX(col3), n=MAX(n), m=MAX(m)

  FROM t

  GROUP BY Col1, Col2 WITH ROLLUP)

  SELECT col1=CASE WHEN col2 IS NULL and m = Col1 THEN 'Col3' ELSE Col1 END

  ,col2=Col4

  FROM t2

  CROSS APPLY (

  SELECT CASE WHEN col2 IS NULL AND m=Col1 THEN Col3 WHEN col2 IS NULL THEN NULL ELSE Col2 END) x(Col4)

  WHERE Col4 IS NOT NULL

  ---

  My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

  My thought question: Have you ever been told that your query runs too fast?

  My advice:
  INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
  The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

  Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
  Since random numbers are too important to be left to chance, let's generate some![/url]
  Learn to understand recursive CTEs by example.[/url]
  [url url=http://www.sqlservercentral.com/articles/St