SQL "Null" problem!
-
Hello everybody, I try to show you the output but when I look from the preview part, the design is get out of order. I want to ask you how can I make the "null" parts gone? There are now empty spaces instead of nulls. But I want namesurname one after another without no spaces. The output of this code is ; MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY B(+) NIGHT BIRCAN AR B(+) NIGHT BIRCAN AR B(+) NIGHT HASAN DEM HASAN DEM B(+) NIGHT HAVVA SUK HAVVA SUK B(+) NIGHT HAVVA SUK B(+) NIGHT MEHDIYE GÖR B(+) NIGHT KEREM GOK Here is my second table's view as I want. What should I do to get this output? MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY B(+) NIGHT BIRCAN AR BIRCAN AR HAVVA SUK HAVVA SUK B(+) NIGHT HASAN DEM HASAN DEM MEHDIYE GÖR B(+) NIGHT HAVVA SUK KEREM GOK This my code; select ISNULL(MAX(MachineType),''), ISNULL(MAX(DAYS),''), ISNULL(MAX(CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END),'') AS MONDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END),'') AS TUESDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END),'') AS WEDNESDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END),'') AS THURSDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END),'') AS FRIDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END),'') AS SATURDAY from (SELECT SICK.MACHINETYPE,SICK.NAMESURNAME , CASE WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS, CASE WHEN SICK.MACHINETYPE= 1 THEN 'Negative-' WHEN SICK.MACHINETYPE= 2 THEN 'B(+)' WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)' WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType, CASE WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY FROM SEANCE,SICK WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' and SUB
-
Hello everybody, I try to show you the output but when I look from the preview part, the design is get out of order. I want to ask you how can I make the "null" parts gone? There are now empty spaces instead of nulls. But I want namesurname one after another without no spaces. The output of this code is ; MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY B(+) NIGHT BIRCAN AR B(+) NIGHT BIRCAN AR B(+) NIGHT HASAN DEM HASAN DEM B(+) NIGHT HAVVA SUK HAVVA SUK B(+) NIGHT HAVVA SUK B(+) NIGHT MEHDIYE GÖR B(+) NIGHT KEREM GOK Here is my second table's view as I want. What should I do to get this output? MacType DAYS MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY B(+) NIGHT BIRCAN AR BIRCAN AR HAVVA SUK HAVVA SUK B(+) NIGHT HASAN DEM HASAN DEM MEHDIYE GÖR B(+) NIGHT HAVVA SUK KEREM GOK This my code; select ISNULL(MAX(MachineType),''), ISNULL(MAX(DAYS),''), ISNULL(MAX(CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END),'') AS MONDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END),'') AS TUESDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END),'') AS WEDNESDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END),'') AS THURSDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END),'') AS FRIDAY, ISNULL(MAX(CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END),'') AS SATURDAY from (SELECT SICK.MACHINETYPE,SICK.NAMESURNAME , CASE WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS, CASE WHEN SICK.MACHINETYPE= 1 THEN 'Negative-' WHEN SICK.MACHINETYPE= 2 THEN 'B(+)' WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)' WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType, CASE WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY' WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY FROM SEANCE,SICK WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' and SUB
Ever heard of CAST and TRIM? If not then google it, and use it! HTH.
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.