SQL trying to do a Row Wise Sum in a complicated SQL pivot and dynamic query

Question

I have a simple Invoice table that has each item sold and the date it was sold.

Here is some sample data of taking the base database and counting how much times each item was sold per week.

+------+-----------------+------------+---------+
| Week |   Item_Number   | Color_Code | Touches |
+------+-----------------+------------+---------+
|    1 | 11073900LRGMO   |      02000 |       7 |
|    1 | 11073900MEDMO   |      02000 |       9 |
|    2 | 1114900011BMO   |      38301 |      62 |
|    2 | 1114910012BMO   |      21701 |     147 |
|    2 | 1114910012BMO   |      38301 |     147 |
|    2 | 1114910012BMO   |      46260 |     147 |
|    3 | 13MK430R03R     |      00101 |       2 |
|    3 | 13MK430R03R     |      10001 |       2 |
|    3 | 13MK430R03R     |      65004 |       8 |
|    3 | 13MK430R03S     |      00101 |       2 |
|    3 | 13MK430R03S     |      10001 |       2 |
+------+-----------------+------------+---------+

Then I created a matrix out of this data using a dynamic query and the pivot operator. Here is how I did that,

First, I create a temporary table

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query  AS NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#VTable') IS NOT NULL
    DROP TABLE #VTable

CREATE TABLE #VTable 
(
  [Item_Number] NVARCHAR(100), 
  [Color_Code] NVARCHAR(100), 
  [Item_Cost] NVARCHAR(100), 
  [Week] NVARCHAR(10), 
  [xCount] int
);

Then I insert my data into that table,

INSERT INTO #VTable
(
  [Item_Number], 
  [Color_Code],
  [Item_Cost],
  [Week], 
  [xCount]
)

SELECT 
*
FROM (
    SELECT
    Item_Number
    ,Color_Code
    ,Item_Cost
    ,Week
    ,Count(Item_Number) Touches
    FROM (

        SELECT

         DATEPART (year, I.Date_Invoiced) Year
        ,DATEPART (month, I.Date_Invoiced) Month
        ,Concat(CASE WHEN DATEPART (week, I.Date_Invoiced) <10 THEN  CONCAT('0',DATEPART (week, I.Date_Invoiced)) ELSE CAST(DATEPART (week, I.Date_Invoiced) AS NVARCHAR) END,'-',RIGHT(DATEPART (year, I.Date_Invoiced),2) )  WEEK
        ,DATEPART (day, I.Date_Invoiced) Day
        ,I.Invoice_Number
        ,I.Customer_Number
        ,I.Warehouse_Code
        ,S.Pack_Type
        ,S.Quantity_Per_Carton
        ,S.Inner_Pack_Quantity
        ,LTRIM(RTRIM(ID.Item_Number)) Item_Number
        ,LTRIM(RTRIM(ID.Color_Code)) Color_Code
        ,CASE 
            WHEN ISNULL(s.Actual_Cost, 0) = 0
                THEN ISNULL(s.Standard_Cost, 0)
                ELSE s.Actual_Cost  
        END Item_Cost
        ,ID.Quantity
        ,case when s.Pack_Type='carton' then id.Quantity/s.Quantity_Per_Carton when  s.Pack_Type='Inner Poly'  then id.Quantity/s.Inner_Pack_Quantity end  qty
        ,ID.Line_Number

        FROM    Invoices I
                LEFT JOIN Invoices_Detail ID on I.Company_Code = ID.Company_Code and I.Division_Code = ID.Division_Code and I.Invoice_Number = ID.Invoice_Number
                LEFT JOIN Style S on I.Company_Code = S.Company_Code and I.Division_Code = S.Division_Code and ID.Item_Number = S.Item_Number and ID.Color_Code = S.Color_Code

        WHERE   1=1
                AND (I.Company_Code = @LocalCompanyCode OR @LocalCompanyCode IS NULL)  
                AND (I.Division_Code = @LocalDivisionCode OR @LocalDivisionCode IS NULL)
                AND (I.Warehouse_Code = @LocalWarehouse OR @LocalWarehouse IS NULL)
                AND (S.Pack_Type = @LocalPackType OR @LocalPackType IS NULL)
                AND (I.Customer_Number = @LocalCustomerNumber OR @LocalCustomerNumber IS NULL)
                AND (I.Date_Invoiced Between @LocalFromDate And @LocalToDate)
    ) T
    GROUP BY Item_Number,Color_Code,Item_Cost,Week
) TT

Then I use a dynamic query to create the matrix:

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from #VTable
                    group by Week
                    order by (Right(Week,2) + LEFT(Week,2))
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = '
                SELECT 
                * 
                FROM (
                    SELECT Item_Number,Color_Code, Item_Cost,' + @cols + ' from 
                     (
                        select Item_Number, Color_Code, Item_Cost, week, xCount
                        from #Vtable
                    ) x
                    pivot 
                    (
                        sum(xCount)
                        for week in (' + @cols + ')
                    ) p 
                )T
            '

execute(@query);

This gives me what I am looking for, here is what the matrix looks like.

+---------------+------------+-----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
|  Item_Number  | Color_Code | Item_Cost | 36-18 | 37-18 | 38-18 | 39-18 | 40-18 | 41-18 | 42-18 | 43-18 | 44-18 | 45-18 | 46-18 | 47-18 | 48-18 | 49-18 | 50-18 | 51-18 | 52-18 | 53-18 | 01-19 | 02-19 | 03-19 | 04-19 | 05-19 | 06-19 | 07-19 | 08-19 | 09-19 | 10-19 | 11-19 | 12-19 | 13-19 | 14-19 | 15-19 | 16-19 | 17-19 | 18-19 | 19-19 | 20-19 | 21-19 | 22-19 | 23-19 | 24-19 | 25-19 | 26-19 | 27-19 | 28-19 | 29-19 | 30-19 | 31-19 | 32-19 | 33-19 | 34-19 | 35-19 |
+---------------+------------+-----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+
| 11073900LRGMO |      02000 | 8.51      |     1 | NULL  |    13 | NULL  |     3 | NULL  | NULL  |     3 |     3 | NULL  |     4 | 3     |     6 | NULL  |     4 | NULL  | NULL  | NULL  |     7 |     4 | NULL  | 3     |     2 |     5 |    30 |     7 |     3 |    10 | NULL  |     9 |    19 |     5 | NULL  |    10 |     9 |     5 |     2 |     3 |     5 |     4 |     3 |     9 |     7 | NULL  |     5 |     1 | 3     |     5 | NULL  | NULL  |    11 |     7 |     3 |
| 11073900MEDMO |      02000 | 8.49      |    11 | NULL  |    22 | NULL  |     5 | NULL  | NULL  |    14 |     4 | NULL  |     4 | 3     |     8 | NULL  |     9 | NULL  | NULL  | NULL  |     9 |     3 | NULL  | 7     |     6 |     4 |    37 |    10 |     8 |     9 | NULL  |     7 |    30 |    14 | NULL  |    12 |     5 |     7 |     8 |     7 |     2 |     4 |     6 |    15 |     4 | NULL  |     2 |     7 | 3     |     7 | NULL  | NULL  |    11 |     9 |     3 |
| 11073900SMLMO |      02000 | 8.50      |     6 | NULL  |    18 | NULL  |     3 | NULL  | NULL  |     3 |     7 | NULL  |     5 | NULL  |     7 | NULL  |     9 | NULL  | NULL  | NULL  |     7 |     4 | NULL  | 7     |     2 |     6 |    37 |     9 |     4 |     7 | NULL  |     7 |    19 |     7 | NULL  |    11 |     5 |     7 |     7 |     2 |     3 |     8 |     8 |     9 |     2 | NULL  |     2 |     2 | 2     |     4 | NULL  | NULL  |     8 |     5 |     4 |
| 11073900XLGMO |      02000 | 8.51      |     2 | NULL  |     6 | NULL  |     3 | NULL  | NULL  |     2 |     4 | NULL  |     3 | 1     |     3 | NULL  |     4 | NULL  | NULL  | NULL  |     4 |     4 | NULL  | NULL  |     3 |     1 |    27 |     4 |     3 |     4 | NULL  |     8 |    11 |     9 | NULL  |     7 |     2 |     4 |     1 |     5 |     1 |     6 |     5 |     6 |     1 | NULL  |     1 |     3 | NULL  |     3 | NULL  | NULL  |     3 |     4 |     2 |
+---------------+------------+-----------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+

The last thing I want to do is find a good way to sort this table. I think the best way to do that would be to sort by which item numbers are picked the most across all weeks. Doing column wise sum will give me the total amount of touches per week for all items, but I want to do a row wise sum where there is another column at the end that has the touches per item. Does anyone know how I would do this? I've tried messing around with another dynamic query from this link -> (calculate Row Wise Sum - Sql server ) but I couldn't get it to work.

Are you just asking how to add an order by to your dynamic sql? — Sean Lange, Oct 02 '19 at 14:37
No, I want to create a new column called something like totalCount that sums every weeks count per item (so row wise) and then order by that column. — nathan, Oct 02 '19 at 14:53
Why not just create another variable like @cols that is delimited with a + instead of a comma? Then you just add that to your dynamic sql and it will add all the columns together. The order by should be trivial at that point. — Sean Lange, Oct 02 '19 at 15:20

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

Here is a quick-and-dirty solution based on this answer to do the "coalesce sum" over your wk-yr columns. This does not modify your existing code, but for efficiency it may be better do as @Sean Lange suggests.

Tested on SQL Server 2017 latest (linux docker image).

Input dataset:

(Only 3 wk-yr columns here for simplicity. The code should work on arbitrary amount of columns):

create table WeeklySum (
    Item_Number varchar(50),
    Color_Code varchar(10),
    Item_Cost float,
    [36-18] float,
    [37-18] float,
    [38-18] float 
)

insert into WeeklySum (Item_Number, Color_Code, Item_Cost, [36-18], [37-18], [38-18])
values ('11073900LRGMO', '02000', 8.51,  1, NULL,  13),
        ('11073900MEDMO', '02000', 8.49, 11, NULL,  22),
        ('11073900SMLMO', '02000', 8.50,  6, NULL,  18),
        ('11073900XLGMO', '02000', 8.51,  2, NULL,   6);

select * from WeeklySum;

Sample Code:

/* 1. Expression of the sum of coalesce(wk-yr, 0) */
declare @s varchar(max);

-- In short, this query select wanted columns by exclusion in sys.columns
-- and then do the "coalesce sum" over the selected columns in a row.
-- The "@s = coalesce()" expression is to avoid redundant '+' at beginning.
-- NOTE: May have to change sys.columns -> syscolumns for SQL Server 2005 
--       or earlier versions
select @s = coalesce(@s + ' + coalesce([' + C.name + '], 0)', 'coalesce([' + C.name + '], 0)')
from sys.columns as C
where C.object_id = (select top 1 object_id from sys.objects
                     where name = 'WeeklySum')
  and C.name not in ('Item_Number', 'Color_Code', 'Item_Cost');
print @s;

/* 2. Perform the sorting query */
declare @sql varchar(max);
set @sql = 'select *, ' + @s + ' as totalCount ' +
    'from WeeklySum ' +
    'order by totalCount desc';
print @sql;
execute(@sql);

Output:

| Item_Number   | Color_Code | Item_Cost | 36-18 | 37-18 | 38-18 | totalCount |
|---------------|------------|-----------|-------|-------|-------|------------|
| 11073900MEDMO | 02000      | 8.49      | 11    | NULL  | 22    | 33         |
| 11073900SMLMO | 02000      | 8.5       | 6     | NULL  | 18    | 24         |
| 11073900LRGMO | 02000      | 8.51      | 1     | NULL  | 13    | 14         |
| 11073900XLGMO | 02000      | 8.51      | 2     | NULL  | 6     | 8          |

Also check the generated expressions on the messages window:

@s:

coalesce([36-18], 0) + coalesce([37-18], 0) + coalesce([38-18], 0) as totalCount

@sql:

select *, coalesce([36-18], 0) + coalesce([37-18], 0) + coalesce([38-18], 0) as totalCount from WeeklySum order by totalCount desc

SQL trying to do a Row Wise Sum in a complicated SQL pivot and dynamic query

1 Answers1