Need How to get the Diagnol elements in the table

  • BEGIN

    create table #temp

    (

    col1 char(2),

    col2 char(2),

    col3 char(2)

    )

    insert #temp

    values('a','','')

    insert #temp

    values('','b','')

    insert #temp

    values('','','c')

    END

    how to get the out put as "abc" from the above code

  • Looks like homework...

    Lookup the NULLIF and COALESCE functions.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I'm not sure why you would ever have to do something like this unless you had a bad data model to start with but also followed some specific rules. Honestly, this looks like a homework question versus something that would happen "real world".

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • aNULLNULL

    NULLbc

    NULLNULLd

    i need out put as "abcd" from above data.please help how to get this done

  • Here is the answer:

    SELECT 'abcd'

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • hi opc . i hope u r funny but the above answer will not suits i think

  • It was a joke, but so are your posts, I thought we were being funny 😛

    Why don't you address ChazMan's thoughts about this not being a real-world scenario?

    If it's homework, that's fine, but show us what you've tried so far. This site is good for learning, but it's not intended for people to get their homework done for them so all they learn is how to use an online forum...what will happen on test day, or on the job!?!

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • hi opc .i Got the Answer with below logic but is there any another best way to achieve this

    BEGIN TRAN

    create table #temp

    (

    col1 varchar(2),

    col2 varchar(2),

    col3 varchar(2)

    )

    insert #temp

    values('a','d','')

    insert #temp

    values('','b','')

    insert #temp

    values('','','c')

    declare @listValues varchar(580)

    create table #tempfinal

    (

    result varchar(10)

    )

    insert into #tempfinal

    select col1 from #temp where isnull(col1,'')<>''

    union

    select col2 from #temp where isnull(col2,'')<>''

    union

    select col3 from #temp where isnull(col3,'')<>''

    DECLARE @STR VARCHAR(2000)

    select @STR = COALESCE(@str , '') + result

    from #tempfinal

    SELECT @STR

    drop table #temp

    drop table #tempfinal

    ROLLBACK

  • That is one way to do it and it is very nice that you decided against using a loop. I suspect there are hundreds of ways to get the task done, with and without using a loop, but "best" is too subjective for me to comment. Suffice it to say that you got the right answer in a set-based manner so you would get an A+ from me 😉

    Are there any other requirements to your assignment?

    We can do this with one query as follows:

    WITH cte(col, row_num)

    AS (

    SELECT col1,

    ROW_NUMBER() OVER (ORDER BY col1)

    FROM (

    SELECT col1

    FROM #temp

    WHERE NULLIF(col1, '') IS NOT NULL

    UNION

    SELECT col2

    FROM #temp

    WHERE NULLIF(col2, '') IS NOT NULL

    UNION

    SELECT col3

    FROM #temp

    WHERE NULLIF(col3, '') IS NOT NULL

    ) tbl

    )

    SELECT [1] + [2] + [3] + [4]

    FROM cte PIVOT ( MAX(col) FOR row_num IN ([1], [2], [3], [4]) ) AS P_ ;

    PS if you stay with your solution you can remove unused variable @listValues.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 9 posts - 1 through 8 (of 8 total)

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