SQL Select - Populate empty column rows

tmajor99
tmajor99 used Ask the Experts™
on
Is it possible to have SQL Select populate columns from other columns conditionally?  For example; I have 1 table with four columns as follows;

Current Week Folder Name   Current-Product-Count              Prior Week Folder Name   Prior-Product-Count
Abrasives                                            98                                                         Abrasives                 100
                                                                                                                           Filters                            9
Conditioner                                    2100                                      
Plugs                                                    31                                                         Plugs                           31

What I want is to populate the empty column rows with either the current or prior week column so the file looks like this:

Current Week Folder Name   Current-Product-Count              Prior Week Folder Name   Prior-Product-Count
Abrasives                                            98                                                         Abrasives                 100
Filters                                                    0                                                         Filters                            9
Conditioner                                    2100                                                        Conditioner                  0
Plugs                                                    31                                                         Plugs                           31

As you can see I want to fill in the blank columns with the value the prior or current week,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lead Software Engineer
Commented:
You can use either the CASE statement, or a combination of NULLIF and ISNULL to do this.
NULLIF & ISNULL Example
SELECT ISNULL(NULLIF(RTRIM([Current Week Folder Name]), ''), [Prior Week Folder Name]) AS [Current Week Folder Name], 
ISNULL(NULLIF(Current-Product-Count, ''), Prior-Product-Count) AS Current-Product-Count
FROM <your table>

Open in new window

CASE Example
SELECT CASE WHEN [Current Week Folder Name] IS NULL OR RTRIM([Current Week Folder Name]) = '' THEN [Prior Week Folder Name] ELSE [Current Week Folder Name] END AS [Current Week Folder Name], 
CASE WHEN Current-Product-Count IS NULL OR RTRIM(Current-Product-Count) = '' THEN Prior-Product-Count ELSE Current-Product-Count END AS Current-Product-Count
FROM <your table>

Open in new window

Looks like the coalesce function is in order here:

select coalesce([Current Week Folder Name],[Prior Week Folder Name]  as ([Current Week Folder Name]
, coalesce( [Current-Product-Count],0) [Current-Product-Count]
, coalesce( [Prior Week Folder Name],[Current Week Folder Name]) [Prior Week Folder Name]
, coalesce( [Prior-Product-Count],0) [Prior-Product-Count]
ste5anSenior Developer

Commented:
Do you really have such a table? Cause when not, then a pivot may be an option:

DECLARE @Sample TABLE
    (
      FolderName VARCHAR(255) ,
      ProductCount INT ,
      WeekNo INT
    );

INSERT  INTO @Sample
VALUES  ( 'Abrasives', 98, 15 ),
        ( 'Conditioner', 2100, 15 ),
        ( 'Plugs', 31, 15 ),
        ( 'Abrasives', 100, 14 ),
        ( 'Filters', 9, 14 ),
        ( 'Plugs', 31, 14 );

DECLARE @CurrentWeekNo INT = 15;

WITH    CurrentWeek
          AS ( SELECT   S.FolderName ,
                        S.ProductCount
               FROM     @Sample S
               WHERE    S.WeekNo = @CurrentWeekNo
             ),
        PreviousWeek
          AS ( SELECT   S.FolderName ,
                        S.ProductCount
               FROM     @Sample S
               WHERE    S.WeekNo = @CurrentWeekNo - 1
             )
    SELECT  *
    FROM    CurrentWeek C
            FULL OUTER JOIN PreviousWeek P ON P.FolderName = C.FolderName;

SELECT  P.FolderName ,
        ISNULL(P.[15], 0) AS CurrentWeekCount ,
        ISNULL(P.[14], 0) AS PreviosWeekCount
FROM    @Sample S PIVOT ( SUM(S.ProductCount) FOR S.WeekNo IN ( [15], [14] ) ) P;

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial