Post reply

TSQL query help

  • October 28, 2014 at 1:48 pm

    #300519

    Hi,

    I have a table with more than 50 columns(column1,column2,column3 .....column60) with 'Y' and 'N' as a value for each row. I want the output to list as for every row- if atleast 1 column has 'Y' then the output for that row should be 'Y'. Can anyone please help, as coalesce is not helping to solve this.

    Sample table:

    Username column1 column2 column3 column4

    ron Y NULL N

    rob NULL N

    rose N N N N

    randy N N Y N

    rocky NULL NULL NULL NULL

    robert

    rosy Y Y Y Y

    robin N N N Y

    ray NULL NULL NULL Y

    output:

    Username Output

    ron Y

    rob N

    rose N

    randy Y

    rocky N

    robert N

    rosy Y

    robin Y

    ray Y

  • October 28, 2014 at 2:50 pm

    #1755363

    The solution to this is messy, but not too technically difficult.

    select Username

    , case

    when (select max(choice) from (

    select column1 as choice

    union all

    select column2

    union all

    select column3

    union all

    select column4

    -- ...

    union all

    select column60

    union all

    select 'N') as choices) = 'Y'

    then 'Y'

    else 'N'

    end as Output

    from MyTable

  • October 28, 2014 at 3:06 pm

    #1755368

    You can use a table valued constructor to simplify it

    select Username

    , (select max(choice) from (VALUES(column1 ), (column2), (column3), (column4),

    -- ...

    (column60), ('N')) as choices) as Output

    from MyTable

    Or you could always use a method to unpivot the table.

    http://qa.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • October 29, 2014 at 3:15 am

    #1755446

    Here's a solution with UNPIVOT:

    DECLARE @sample TABLE (

    Username varchar(50) PRIMARY KEY,

    column1 char(1),

    column2 char(1),

    column3 char(1),

    column4 char(1)

    )

    INSERT INTO @sample VALUES

    ('ron', 'Y', NULL, 'N', NULL)

    ,('rob', NULL, NULL,'N', NULL)

    ,('rose', 'N', 'N', 'N', 'N')

    ,('randy', 'N', 'N', 'Y', 'N')

    ,('rocky', NULL, NULL, NULL, NULL)

    ,('robert', NULL, NULL, NULL, NULL)

    ,('rosy', 'Y', 'Y', 'Y', 'Y')

    ,('robin', 'N', 'N', 'N', 'Y')

    ,('ray', NULL, NULL, NULL, 'Y')

    SELECT Username, MAX(value) AS [output]

    FROM @sample

    UNPIVOT (value FOR attribute IN (column1, column2, column3, column4)) AS U

    GROUP BY Username

    Looks quite efficient.

    -- Gianluca Sartori

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

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