can anybody help me to solve this issue

  • 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

  • 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
  • hi

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply