Pivot/Transpose rows into column and convert NULLs into 0's

Question

I have some data that looks like this:

    Table1
    Number        TYPE         Acct         Total
    ------        ---          ---          ----
    1X2           GGG          111          100
    1X2           GGG          222          200

What I'm trying to do is PIVOT this data so that it looks like this:

   Number         Type         111          222
   -----          ---          ---          ---
   1X2            GGG          100          200

Here's how I pivot:

   Select * from Table1
                 PIVOT (MAX(Total)
                    FOR ACCT in ([111],[222],[333])

Now this works very well for Acct 111 and 222, but for 333 the Total = NULL. Thing is here, that I might sometimes have all three ACCTs, as in 111, 222, and 333. Other times as shown in the above example one of them might be missing.

Anywho, when I do my pivot the data looks like this:

       Number         Type         111          222        333
       -----          ---          ---          ---        ---
       1X2            GGG          100          200        NULL <-- I'm trying to set this to 0

As you can see, the Value for 333 is NULL - IS there anyway I can set this value to 0?

user_1570000 · Answer

Two options. The first will use coalesce() to eliminate the null values. The second will create a a unique set of intersections via a CROSS JOIN and a UNION ALL (brute force)

Example

Declare @YourTable Table ([Number] varchar(50),[TYPE] varchar(50),[Acct] varchar(50),[Total] varchar(50))
Insert Into @YourTable Values 
 ('1X2','GGG',111,100)
,('1X2','GGG',222,200)

Select Number
      ,Type
      ,[111]  = coalesce( [111] ,0)
      ,[222]  = coalesce( [222] ,0)
      ,[333]  = coalesce( [333] ,0)
 From @YourTable
 PIVOT (MAX(Total)
 FOR ACCT in ([111],[222],[333])) pvt

Second Option:

You may notice I supplied the Accts in the last CROSS JOIN because 333 was not in the scope of the sample data. If it will exist, you could use the Select Distinct Acct like we used in Cross Apply 1 and 2

Select *
 From ( Select * from @YourTable
        Union All
        Select Number,Type,Acct,0
         From  ( Select distinct Number  from @YourTable ) A
         Cross Join (Select distinct Type from @YourTable) B
         Cross Join (
                        Select * from  (values ('111' )
                                              ,('222' )
                                              ,('333' )
                                       )v(Acct)
                    ) C
      ) src
 PIVOT (MAX(Total)
 FOR ACCT in ([111],[222],[333])) pvt

Both Results would be

Pivot/Transpose rows into column and convert NULLs into 0's

1 Answers