Multiple rows in to Single row

Ram 21 Reputation points

Hi < I am looking for an help in converting Multiple Rows in to single row, If any of the colum1_Col5 is 'Y' then it should be 'Y' ELSE 'N', Please see the input and output results in screenshot ![71167-image.png][1] ![71188-image.png][2] [1]: /api/attachments/71167-image.png?platform=QnA [2]: /api/attachments/71188-image.png?platform=QnA

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points

    If every Col column must have either a 'Y' or a 'N', then

    Select ID,
      Max(Col1) As Col1,
      Max(Col2) As Col2,
      Max(Col3) As Col3,
      Max(Col4) As Col4,
      Max(Col5) As Col5
    From <your table name>
    Group By ID;

    If Col columns can have other values (like '' or NULL, etc) then

    Select ID,
      Max(Case When Col1 = 'Y' Then 'Y' Else 'N' End) As Col1,
      Max(Case When Col2 = 'Y' Then 'Y' Else 'N' End) As Col2,
      Max(Case When Col3 = 'Y' Then 'Y' Else 'N' End) As Col3,
      Max(Case When Col4 = 'Y' Then 'Y' Else 'N' End) As Col4,
      Max(Case When Col5 = 'Y' Then 'Y' Else 'N' End) As Col5
    From <your table name>
    Group By ID;


    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful