Return a specific string based on positions of Y/N data

I don't know if this is the best solution but I was able to get what I need
as follows

select * ,
case when len(cSUM) = 1 then cSUM
when len(cSUM) = 2 then cSUM
when len(cSUM) = 3 then substring(cSUM,1,1)+substring(cSUM,3,1)
when len(cSUM) = 4 then substring(cSUM,1,1)+substring(cSUM,4,1) end cSUM1
from (
select
case when c1 = 'y' then 'A' else '' end c1
, case when c2 = 'y' then 'B' else '' end c2
, case when c3 = 'y' then 'C' else '' end c3
, case when c4 = 'y' then 'D' else '' end c4
,case when c1 = 'y' then 'A' else '' end +
case when c2 = 'y' then 'B' else '' end +
case when c3 = 'y' then 'C' else '' end +
case when c4 = 'y' then 'D' else '' end CSUM
FROM AdtJuvVSAMFiles
where c1 <>'N' or c2 <>'N' or c3 <>'N' or c4 <>'N'
) t


"NetNewbie" wrote:

> Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> char(1) data which either have Y or N.
>
> Now the task is I need to update a column in a different table based on
> foll. conditions.
>
> treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order if
> the data in the 4 columns is
> - YNNN, then return A
> - YNYN then return AC
> - YNNY then return AD
> - YYYY then return AD (max and min) A being the max and D beign the min.
> - YYYN then return AC (max and min)
>
> I hope I made my question clear. What is the best way to achieve this in
> T-SQL if it can be without writing a long series of CASE statements
>
> The data in T! has been loaded from a flatfile. I can re-load these 4 single
> char columns into 1 column of char(4) if that makes the task easy. To start
> with i loaded into 4 columns.
>
> Thank you for your time

NetNewbie wrote:
> Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> char(1) data which either have Y or N.
>
> Now the task is I need to update a column in a different table based on
> foll. conditions.
>
> treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order if
> the data in the 4 columns is
> - YNNN, then return A
> - YNYN then return AC
> - YNNY then return AD
> - YYYY then return AD (max and min) A being the max and D beign the min.
> - YYYN then return AC (max and min)
>
> I hope I made my question clear. What is the best way to achieve this in
> T-SQL if it can be without writing a long series of CASE statements
>
This works, but it's cheating slightly for the sake of avoiding any sort of
conditional completely. I don't actually suggest you use this.

LEFT(
SUBSTRING('ABCD', CHARINDEX('Y', C1 + C2 + C3 + C4), 1) +
SUBSTRING('DCBA', CHARINDEX('Y', C4 + C3 + C2 + C1), 1),
2 - (CHARINDEX('Y', C1 + C2 + C3 + C4) + CHARINDEX('Y', C4 + C3 + C2 +
C1)) / 5
)

Too clever by half. Plamen's suggestions are more sensible.

--
J.

Hello Plamen,

Your solutions are very interesting. I like them. the 1st solution was easy
to follow and is much cleaner to look at than mine. I did know of the left
and right functions in T-sql and NullIf I have seen that a long time ago, but
did not remember about it

the 2nd solution, can you explain a little bit. The second part of the query
where you specify "AS T(code)" is that an alias or is it supposed to be a
parameter?

I haven't used the pivot/unpivot functions so far though i know it is
available.

Thanks,

"Plamen Ratchev" wrote:

> Here are two solutions. The first one is based on string manipulation and will be more efficient. The second uses
> unpivoting technique.
>
> CREATE TABLE Foo (
> keycol INT NOT NULL PRIMARY KEY,
> col1 CHAR(1) NOT NULL,
> col2 CHAR(1) NOT NULL,
> col3 CHAR(1) NOT NULL,
> col4 CHAR(1) NOT NULL);
>
> INSERT INTO Foo VALUES(1, 'Y', 'N', 'N', 'N');
> INSERT INTO Foo VALUES(2, 'Y', 'N', 'Y', 'N');
> INSERT INTO Foo VALUES(3, 'Y', 'N', 'N', 'Y');
> INSERT INTO Foo VALUES(4, 'Y', 'Y', 'Y', 'Y');
> INSERT INTO Foo VALUES(5, 'Y', 'Y', 'Y', 'N');
>
> SELECT keycol, CASE WHEN LEFT(val, 1) = RIGHT(val, 1)
> THEN LEFT(val, 1)
> ELSE LEFT(val, 1) + RIGHT(val, 1)
> END AS result
> FROM (
> SELECT keycol,
> REPLACE(
> COALESCE(NULLIF(col1, 'Y'), 'A') +
> COALESCE(NULLIF(col2, 'Y'), 'B') +
> COALESCE(NULLIF(col3, 'Y'), 'C') +
> COALESCE(NULLIF(col4, 'Y'), 'D'), 'N', '') AS val
> FROM Foo) AS T;
>
> SELECT keycol, CASE WHEN MIN(val) = MAX(val)
> THEN MIN(val)
> ELSE MIN(val) + MAX(val)
> END AS result
> FROM (
> SELECT keycol,
> CASE code WHEN 'A' THEN COALESCE(NULLIF(col1, 'Y'), code)
> WHEN 'B' THEN COALESCE(NULLIF(col2, 'Y'), code)
> WHEN 'C' THEN COALESCE(NULLIF(col3, 'Y'), code)
> WHEN 'D' THEN COALESCE(NULLIF(col4, 'Y'), code)
> END AS val
> FROM Foo AS F
> CROSS JOIN (SELECT 'A' UNION
> SELECT 'B' UNION
> SELECT 'C' UNION
> SELECT 'D') AS T(code)) AS T
> WHERE val <> 'N'
> GROUP BY keycol;
>
> /*
>
> keycol result
> ----------- ------
> 1 A
> 2 AC
> 3 AD
> 4 AD
> 5 AC
>
> */
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>

What to do with this sample data?

INSERT INTO Foo VALUES(0, 'Y', 'N', 'Y', 'Y');

Should it be returned as "ACD"?


"NetNewbie" wrote in message
news:FA801DF3-5807-43DD-AA51-3BC91DEE1AE0atmicrosoftdotcom...
> Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> char(1) data which either have Y or N.
>
> Now the task is I need to update a column in a different table based on
> foll. conditions.
>
> treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order
> if
> the data in the 4 columns is
> - YNNN, then return A
> - YNYN then return AC
> - YNNY then return AD
> - YYYY then return AD (max and min) A being the max and D beign the min.
> - YYYN then return AC (max and min)
>
> I hope I made my question clear. What is the best way to achieve this in
> T-SQL if it can be without writing a long series of CASE statements
>
> The data in T! has been loaded from a flatfile. I can re-load these 4
> single
> char columns into 1 column of char(4) if that makes the task easy. To
> start
> with i loaded into 4 columns.
>
> Thank you for your time

The output for that (YNYY) should be AD

"Peso" wrote:

> What to do with this sample data?
>
> INSERT INTO Foo VALUES(0, 'Y', 'N', 'Y', 'Y');
>
> Should it be returned as "ACD"?
>
>
> "NetNewbie" wrote in message
> news:FA801DF3-5807-43DD-AA51-3BC91DEE1AE0atmicrosoftdotcom...
> > Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> > char(1) data which either have Y or N.
> >
> > Now the task is I need to update a column in a different table based on
> > foll. conditions.
> >
> > treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order
> > if
> > the data in the 4 columns is
> > - YNNN, then return A
> > - YNYN then return AC
> > - YNNY then return AD
> > - YYYY then return AD (max and min) A being the max and D beign the min.
> > - YYYN then return AC (max and min)
> >
> > I hope I made my question clear. What is the best way to achieve this in
> > T-SQL if it can be without writing a long series of CASE statements
> >
> > The data in T! has been loaded from a flatfile. I can re-load these 4
> > single
> > char columns into 1 column of char(4) if that makes the task easy. To
> > start
> > with i loaded into 4 columns.
> >
> > Thank you for your time
>
> .
>