TSQL query help

Question

Post reply

TSQL query help

Dhruva_51

SSChampion

Points: 10521
More actions
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:
Usernamecolumn1column2column3column4
ronYNULL N
rob NULL N
roseNNNN
randyNNYN
rockyNULLNULLNULLNULL
robert
rosyYYYY
robinNNNY
rayNULLNULLNULLY
output:
UsernameOutput
ronY
robN
roseN
randyY
rockyN
robertN
rosyY
robinY
rayY

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

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

sqlslacker SSChasing Mays Points: 657 More actions · Answer 1

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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://www.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

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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