DynDeveloper.com

DynDeveloper.com
Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now! Log In
Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
acabot 12/1/2016 5:46:34 PM

Getting Rid of NULLs in T-SQL PIVOT

I have this query:

select *
from @eMonth1
PIVOT (Sum([Month1 Emp Count]) FOR ChekDate IN ([07/01/16], [07/08/16], [07/15/16], [07/22/16], [07/29/16])) AS Month1Pivot

@eMonth1 Table Structure is:

declare @eMonth1 table (RowID int identity, Company varchar(10), Division varchar(3), ChekDate varchar(12), [Month1 Emp Count] int, GrossWages money)

Producing this (partial) result:

RowID Company Division GrossWages 7/1/2016 7/8/2016 7/15/2016 7/22/2016 7/29/2016
1 SIZE 120 3858.75 14 NULL NULL NULL NULL
2 SIZE 120 4769.56 NULL 14 NULL NULL NULL
3 SIZE 120 6483.96 NULL NULL 16 NULL NULL
4 SIZE 120 5511 NULL NULL NULL 16 NULL
5 SIZE 120 5139.88 NULL NULL NULL NULL 16
6 SIZE 150 64042.61 183 NULL NULL NULL NULL
7 SIZE 150 66992.7 NULL 185 NULL NULL NULL
8 SIZE 150 66435.72 NULL NULL 185 NULL NULL
9 SIZE 150 62072.04 NULL NULL NULL 178 NULL
10 SIZE 150 55925.73 NULL NULL NULL NULL 162
11 SIZE 150 7552.88 18 NULL NULL NULL NULL
12 SIZE 150 7637.16 NULL 18 NULL NULL NULL
13 SIZE 150 6992.15 NULL NULL 19 NULL NULL
14 SIZE 150 9934.28 NULL NULL NULL 22 NULL
15 SIZE 150 9578.31 NULL NULL NULL NULL 24
16 SIZE 150 21507.97 118 NULL NULL NULL NULL
17 SIZE 150 21447.67 NULL 115 NULL NULL NULL
18 SIZE 150 20429.94 NULL NULL 116 NULL NULL
19 SIZE 150 21539.67 NULL NULL NULL 118 NULL
20 SIZE 150 22262.33 NULL NULL NULL NULL 120

 

But I'm not sure how to eliminate the NULL cells.  I'm also going to try and make the ChekDate columns dynamic, but first things first.

Any insights will be appreciated -- it's something I've never tried outside of MS Access.

       
       
       
       
       
       
       
       
       
       
       

Version: GP 2013
Section: SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
Archives | Table Listing | eConnect Error Database | eConnect Schema | Gold Star Locksmith
3