CODEWITHSUNDEEP
sql-servercase
Gloria Santin
Gloria Santin

Reputation: 2136

Consolidating rows in SQL using CASE statement

This is my current results of my query: enter image description here

I need to rename the Trimester column to 'FirstTri', if the Trimester value is either 'FirstVisit1HealthCenter' or 'FirstVisit1OtherProvider'. If the values in the Trimester column are 'FirstVisit2HealthCenter' or 'FirstVisit2OtherProvider' then the Trimester column is 'SecondTri'. Same with the rows with a '3'. The column will be 'ThirdTri'.

The values in the column 'First Visit with Health Center' are not null, those values need to be returned. The values in the column 'First Visit with Other Provider' that are not null also need to be kept.

enter image description here

This is the SELECT statement that I have but it keeps returning the values that are NULL

SELECT DISTINCT 6 + ROW_NUMBER() OVER (ORDER BY Trimester) AS Line, 
        CASE WHEN Trimester IN ('FirstVisit1HealthCenter', 'FirstVisit1OtherProvider') THEN 'First Trimester'
             WHEN Trimester IN ('FirstVisit2HealthCenter', 'FirstVisit2OtherProvider') THEN 'Second Trimester'
             WHEN Trimester IN ('FirstVisit3HealthCenter', 'FirstVisit3OtherProvider') THEN 'Third Trimester' END AS EarlyEntry, 
        CASE WHEN [First Visit with Health Center] IS NOT NULL THEN [First Visit with Health Center] END AS [First Visit with Health Center],
        CASE WHEN [First Visit with Other Provider] IS NOT NULL THEN [First Visit with Other Provider] END AS [First Visit with Other Provider] 
--SELECT *
FROM #tblSubResult

How do I NOT return the rows that are NULL but only return 3 rows?

Upvotes: 0

Views: 394

Answers (1)

MikeS
MikeS

Reputation: 1764

You could could make your query a sub query and Sum the columns:

select EarlyEntry, 
sum([First Visit with Health Center]) as [First Visit with Health Center], 
sum([First Visit with Other Provider]) as [First Visit with Other Provider]  
from (
        SELECT DISTINCT 6 + ROW_NUMBER() OVER (ORDER BY Trimester) AS Line, 
                CASE WHEN Trimester IN ('FirstVisit1HealthCenter', 'FirstVisit1OtherProvider') THEN 'First Trimester'
                     WHEN Trimester IN ('FirstVisit2HealthCenter', 'FirstVisit2OtherProvider') THEN 'Second Trimester'
                     WHEN Trimester IN ('FirstVisit3HealthCenter', 'FirstVisit3OtherProvider') THEN 'Third Trimester' END AS EarlyEntry, 
                CASE WHEN [First Visit with Health Center] IS NOT NULL THEN [First Visit with Health Center] END AS [First Visit with Health Center],
                CASE WHEN [First Visit with Other Provider] IS NOT NULL THEN [First Visit with Other Provider] END AS [First Visit with Other Provider] 
        --SELECT *
        FROM #tblSubResult) a
        group by EarlyEntry

Upvotes: 1

Related Questions