none
More PIVOT and Invalid Column Name issues RRS feed

  • Question

  • I built a CTE around a long UNION query and populated a Temp table with the results.

    Then I built a PIVOT query on the temp table, but the results are a little unsatisfactory because the data is a little sparse and the data filled with many big, ugly NULL values.

    I'd rather see 0 values than the Nulls for my dashboarding purposes.

    However, when I take this query:

    SELECT TableName, [2019-11-20],[2019-11-19],[2019-11-18],[2019-11-15],[2019-11-14],...[2019-09-24],[2019-09-23]

    FROM (SELECT TP.TableName, TP.PATCount,TP.DailyDate  FROM #TempPRATC TP ) as Temp

    PIVOT (Sum(Temp.PATCount ) FOR Temp.[DailyDate] IN ([2019-11-20],[2019-11-19],[2019-11-18],...[2019-09-23])) As pvt

    ...and try to supplant the NULLs with 0s, I get 'Invalid Column Name' errors for the expression column in 'Sum(Temp.PATCount )':

    SELECT TableName, [2019-11-20],[2019-11-19],[2019-11-18],[2019-11-15],[2019-11-14],...[2019-09-24],[2019-09-23]

    FROM (SELECT TP.TableName, IsNull(TP.PATCount,0) as PATCount,TP.DailyDate  FROM #TempPRATC TP ) as Temp

    PIVOT (Sum(Temp.PATCount ) FOR Temp.[DailyDate] IN ([2019-11-20],[2019-11-19],[2019-11-18],...[2019-09-23])) As pvt

    Any advice appreciated - nothing I tried (a different column alias, etc.) seemed to help, and neither did:

    Sum(isnull(Temp.PATCount,0))

    (the ellipses in the queries above is just me omitting many other date values for brevity here.)


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)


    • Edited by Mark Burns - PMADN Wednesday, November 20, 2019 11:00 PM added note on ellipses
    Wednesday, November 20, 2019 10:58 PM

Answers

  • SELECT TableName, isnull([2019-11-20],0) as [2019-11-20],

    isnull([2019-11-19],0) as [2019-11-19],

    isnull([2019-11-18],0) as [2019-11-18],

    ...

    from (

    )

    ...


    Wednesday, November 20, 2019 11:19 PM
    Moderator

All replies

  • SELECT TableName, isnull([2019-11-20],0) as [2019-11-20],

    isnull([2019-11-19],0) as [2019-11-19],

    isnull([2019-11-18],0) as [2019-11-18],

    ...

    from (

    )

    ...


    Wednesday, November 20, 2019 11:19 PM
    Moderator
  • Hi Mark,

    Also , you can use CASE WHEN to achieve your requirement.

    SELECT TableName, 
    
    case when [2019-11-20] is null then 0 else [2019-11-20] end [2019-11-20],
    
    case when [2019-11-19] is null then 0 else [2019-11-19] end  [2019-11-19],
    
    case when [2019-11-18] is null then 0 else [2019-11-18] end   [2019-11-18],
    
    ...
    
    from (
    
    )
    
    
    ...

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 21, 2019 7:08 AM
  • Jingyang,

    Thanks for the answer, but awww Crud!! I'm already dynamically building the SQL for the Pivot's dates, and this just makes it even ickier.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

    Thursday, November 21, 2019 6:19 PM
  • Create a new columns list with isnull around the column names. The pivot part you are using your current list and the select part, you are using the columns list.

    A little better is to use dynamic case to pivot your data and you need only one column list with the isnull function included.

    Thursday, November 21, 2019 6:50 PM
    Moderator
  • Jingyang,

    Thanks for the answer, but awww Crud!! I'm already dynamically building the SQL for the Pivot's dates, and this just makes it even ickier.


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

    I found one sample from my early posting. I posted here for your reference.

    If you have a table DDL and sample data scripts, I can help to come up with a query to pivot your data with dynamic case expression.

    Here is the sample:

    --another example:
    Create table Capacity(Name char(1),Value int)
    Insert into Capacity values('A',6),('B',6),('C',5)
    Create table Completed (Name char(1), Date date, Value int)
    Insert into Completed values('A','18 jan,2017', 5)
    ,('A','19 jan,2017', 3)
    ,('B','17 jan,2017', 4)
    ,('B','17 jan,2017', 4)
    ,('B','20 jan,2017', 2)
     
    declare @startdate date='17 Jan 2017'
    declare @enddate date= '20 Jan 2017'
     
     
    DECLARE @Sql NVARCHAR(4000) =null
        
    declare @ColumnHeaders NVARCHAR(4000) ;
    ;with dates as (
    Select dateadd(day,n ,@startdate ) dt 
    from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
    Where dateadd(day,n ,@startdate )<=@enddate)
     
     
    --load date list to a temp table
    Select dt 
    into tempdates
    from dates
     
    Select @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value1  else null end ) as '
    + quotename(Cast(datepart(day,dt) as varchar(2))+'Capacity','[')  + char(10)+char(13)
    +
    ',' + 'isnull(max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value2  else null end ),0) as '
    + quotename(Cast(datepart(day,dt) as varchar(2))+'Complete','[') + char(10)+char(13)
    FROM  tempdates
    FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
     
     --print @ColumnHeaders
     
     
    ;with  names as (
    Select distinct Name from Capacity
    )
    ,combs as (
    Select Name, dt from names,tempdates)
     
    ,finaldataset as (
    Select c0.name,c0.dt,c1.value as value1, c2.value as value2 from combs  c0 join Capacity c1 on c0.name=c1.name
    left join  Completed c2 on c0.name=c2.name and c0.dt=c2.Date
    )
     
     
    Select * into finaldataset2
    from finaldataset
     
    Select @sql  =N' Select  name, '+   @ColumnHeaders + ' from  finaldataset2 Group by name';
           
    --print @sql
    EXEC(@SQL)
     
    --cleanup
    Drop table finaldataset2,tempdates
     
    drop table Completed,Capacity
     
     

    Thursday, November 21, 2019 6:56 PM
    Moderator
  • Could you please share us script ? Maybe we can help you to modify your script. The modifications won't be complicated.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 22, 2019 6:54 AM
  • All,

    Thanks to @Jingyang Li  for the solution to my woes.

    Here is my completed script/answer:

    Declare @Cols nvarchar(max), @len INT;
    Declare @SQL nvarchar(max);
    Declare @ColHeads nvarchar(max);
    
    Select @Cols = (
    	SELECT DISTINCT top 14 cast(cast([CreateTime] as DATE) as varchar) As DailyDate
    		FROM ProdServer.Data.datahost.tblItemDetail		-just grabbed a prod table I know get daya daily, no skipped days
    	--  WHERE cast([CreateTime] as DATE) >= cast(DateAdd(d, -13, getdate()) as date)
    	-- I originally went with the calendar dates, but Top 14 got me more consistent resilts due to weekends, etc.
    ORDER BY DailyDate Desc
    		 FOR XML Path('')
    )
    
    Set @Cols = replace(replace(@Cols, N'<DailyDate>', N'['), N'</DailyDate>', N'],')
    SET @ColHeads = ',' + @Cols;
    set @Cols = left(@Cols, len(@Cols) - 1)
    /* results in @Cols='[YYYY-MM-DD],[YYYY-MM-DD-1],[YYYY-MM-DD-2],[YYYY-MM-DD-3]...'  */
    SET @len = LEN(@ColHeads);
    -- Print N'@Cols = ' + @Cols
    
    DECLARE @nums TABLE (n int);
    INSERT INTO @nums (n)
        SELECT A.n FROM 
        (SELECT ROW_NUMBER() OVER (ORDER BY AO.object_id, SAO.object_id) as n FROM sys.objects AO CROSS join sys.objects SAO  ) A
        WHERE A.n BETWEEN 1 AND @len;
    
    Declare @DailyDates Table (dd nvarchar(20));
    insert into @DailyDates (dd)
    SELECT SUBSTRING(@ColHeads , n + 1, CHARINDEX( ',', @ColHeads, n + 1 ) - n - 1 ) AS "value"
        FROM @nums
        WHERE SUBSTRING( @ColHeads, n, 1 ) = ',' AND n < @len;
    
    Set @ColHeads = (Select 'IsNull(' + dd + ', 0) as ' + dd + ',' from @DailyDates FOR XML Path(''))
    set @ColHeads = left(@ColHeads, len(@ColHeads) - 1)
    /* results in @ColHeads = 'Isnull([YYYY-MM-DD], 0) As [YYYY-MM-DD],Isnull([YYYY-MM-DD - 1], 0) As [YYYY-MM-DD - 1],Isnull([YYYY-MM-DD - 2], 0) As [YYYY-MM-DD - 2]...'  */
    -- Print N'@ColHeads = ' + @ColHeads
    
    CREATE TABLE #TempPRATC (
    	TableName varchar(50) NOT NULL,
    	PATCount int NOT NULL,
    	PRTCount int NOT NULL,
    	DailyDate Date NOT NULL,
    	Ok tinyint NOT NULL
    ) 
    
    set @SQL = '
    ;With ProdArchiveTableCounts As
    (
    	SELECT ''tblUnitHeader'' as [TableName], ISNULL(PAT.PACount, 0) as PATCount,  
    				 PRT.PRCount as PRTCount, IsNull(PRT.PRDATE, PAT.PADATE) as [DailyDate], 
    				 case WHEN (isnull(PAT.PACount,0) = PRT.PRCount) THEN 1 Else 0 END as [Ok]
    		FROM (SELECT Count(*) as PACount, cast(PA.[CreateTime] as DATE) as [PADATE] FROM datahost.tblUnitHeader PA GROUP BY cast(PA.[CreateTime] as DATE)) AS PAT 
    		     FULL OUTER JOIN (SELECT Count(*) as PRCount, cast(PR.[CreateTime] as DATE) as [PRDATE] FROM ProdServer.Data.datahost.tblUnitHeader PR with (NOLOCK) GROUP BY cast(PR.[CreateTime] as DATE)) AS PRT
    			 	 ON PAT.PADATE = PRT.PRDATE
    GROUP BY IsNull(PRT.PRDATE, PAT.PADATE), PAT.PACount, PRT.PRCount, case WHEN (isnull(PAT.PACount,0) = PRT.PRCount) THEN 1 Else 0 END
    UNION
    	SELECT ''tblParcel'' as [TableName], ISNULL(PAT.PACount, 0) as PATCount,
             PRT.PRCount as PRTCount, IsNull(PRT.PRDATE, PAT.PADATE) as [DailyDate], 
    				 case WHEN (isnull(PAT.PACount,0) = PRT.PRCount) THEN 1 Else 0 END as [Ok]
    		FROM (SELECT Count(*) as PACount, cast(PA.[CreateTime] as DATE) as [PADATE] FROM datahost.tblParcel PA with (NOLOCK) GROUP BY cast(PA.[CreateTime] as DATE)) AS PAT 
    		     FULL OUTER JOIN (SELECT Count(*) as PRCount, cast(PR.[CreateTime] as DATE) as [PRDATE] FROM ProdServer.Data.datahost.tblParcel PR with (NOLOCK) GROUP BY cast(PR.[CreateTime] as DATE)) AS PRT
    				 ON PAT.PADATE = PRT.PRDATE
    GROUP BY IsNull(PRT.PRDATE, PAT.PADATE), PAT.PACount, PRT.PRCount, case WHEN (isnull(PAT.PACount,0) = PRT.PRCount) THEN 1 Else 0 END
    UNION
    -- snipping out a bunch more tables for brevity....this is a LONG UNION query!
    UNION'
    SET @SQL = @SQL + '		-- > 4000 chars so this was necessary (snipped ome of these too)
    	 SELECT ''tblUserLog'' as [TableName], ISNULL(PAT.PACount, 0) as PATCount, 
    	        PRT.PRCount as PRTCount, IsNull(PRT.PRDATE, PAT.PADATE) as [DailyDate], 
    					case WHEN (isnull(PAT.PACount,0) = PRT.PRCount) THEN 1 Else 0 END as [Ok]
    		FROM (SELECT Count(*) as PACount, cast(PA.[CreateTime] as DATE) as [PADATE] FROM datahost.tblUserLog PA GROUP BY cast(PA.[CreateTime] as DATE)) AS PAT 
    		     FULL OUTER JOIN (SELECT Count(*) as PRCount, cast(PR.[CreateTime] as DATE) as [PRDATE] FROM ProdServer.Data.datahost.tblUserLog PR with (NOLOCK) GROUP BY cast(PR.[CreateTime] as DATE)) AS PRT
    				 ON PAT.PADATE = PRT.PRDATE
    GROUP BY IsNull(PRT.PRDATE, PAT.PADATE), PAT.PACount, PRT.PRCount, case WHEN (isnull(PAT.PACount,0) = PRT.PRCount) THEN 1 Else 0 END
    )
    INSERT INTO #TempPRATC (TableName, PATCount, PRTCount, [DailyDate], [Ok])
    SELECT TableName, PATCount, PRTCount, [DailyDate], [Ok]
    	FROM ProdArchiveTableCounts;'
    execute(@SQL);
    -- Archive Table Counts
    set @SQL = 'SELECT TableName as [Archive Tables], ' + @ColHeads + '
    							FROM (SELECT TP.TableName, TP.PATCount, TP.DailyDate FROM #TempPRATC TP ) as Temp
    						 PIVOT (Sum(Temp.PATCount) FOR Temp.[DailyDate] IN (' + @Cols+ ')) As pvt
    					ORDER BY TableName;'
    execute(@SQL);
    
    -- PROD Table Counts
    set @SQL = 'SELECT TableName as [PROD Tables], ' + @ColHeads + '
    							FROM (SELECT TP.TableName, TP.PRTCount, TP.DailyDate FROM #TempPRATC TP ) as Temp
    						 PIVOT (Sum(Temp.PRTCount) FOR Temp.[DailyDate] IN (' + @Cols+ ')) As pvt
    					ORDER BY TableName;'
    execute(@SQL);
    
    -- Deltas  (0 = mismatch, 1 = match, NULL = nothing to check)
    set @SQL = 'SELECT TableName as [Count Deltas], ' + @Cols + '
    							FROM (SELECT TP.TableName, TP.Ok, TP.DailyDate FROM #TempPRATC TP ) as Temp
    						 PIVOT (Max(Temp.Ok) FOR Temp.[DailyDate] IN (' + @Cols+ ')) As pvt
    					ORDER BY TableName;'
    execute(@SQL);
    
    DROP Table #TempPRATC;


    Mark Burns, MCAD, MCP
    Sr. Microsoft Access Analyst/Developer
    Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)



    Friday, November 22, 2019 9:56 PM