Pivot table returns multiple rows with NULLs, are not grouped on one row

Question

This is my raw data:

CustomConfigName    ColumnNumber    ColumnName          groupingID  Value
SAPTotals           1               Batch_Reference     35          BATCH001
SAPTotals           2               Bank_Account        35          72368837
SAPTotals           3               Sequence_No         35          123
SAPTotals           4               Entry_Date          35          29/01/2019
SAPTotals           5               Opening_Balance     35          2458.65
SAPTotals           6               Closing_Balance     35          2587.99
SAPTotals           7               Total_Credits       35          76
SAPTotals           8               Total_Debits        35          34
SAPTotals           9               Impex_No            35          I907
SAPTotals           10              Export_Date         35          01/02/2019
SAPTotals           1               Batch_Reference     36          BATCH002
SAPTotals           2               Bank_Account        36          72368837
SAPTotals           3               Sequence_No         36          124
SAPTotals           4               Entry_Date          36          30/01/2019
SAPTotals           5               Opening_Balance     36          2587.99
SAPTotals           6               Closing_Balance     36          2308.32
SAPTotals           7               Total_Credits       36          154
SAPTotals           8               Total_Debits        36          776
SAPTotals           9               Impex_No            36          I907
SAPTotals           10              Export_Date         36          02/02/2019
SAPTotals           1               Batch_Reference     37          BATCH003
SAPTotals           2               Bank_Account        37          72368837
SAPTotals           3               Sequence_No         37          125
SAPTotals           4               Entry_Date          37          31/01/2019
SAPTotals           5               Opening_Balance     37          2308.32
SAPTotals           6               Closing_Balance     37          4219.37
SAPTotals           7               Total_Credits       37          345
SAPTotals           8               Total_Debits        37          87
SAPTotals           9               Impex_No            37          I907
SAPTotals           10              Export_Date         37          
SAPTotals           1               Batch_Reference     38          BATCH566
SAPTotals           2               Bank_Account        38          73297699
SAPTotals           3               Sequence_No         38          344
SAPTotals           4               Entry_Date          38          30/01/2019
SAPTotals           5               Opening_Balance     38          1987.66
SAPTotals           6               Closing_Balance     38          4588.22
SAPTotals           7               Total_Credits       38          67
SAPTotals           8               Total_Debits        38          98
SAPTotals           9               Impex_No            38          I907
SAPTotals           10              Export_Date         38          04/02/2019
SAPTotals           1               Batch_Reference     39          BATCH_ABC
SAPTotals           2               Bank_Account        39          73297699
SAPTotals           3               Sequence_No         39          345
SAPTotals           4               Entry_Date          39          31/01/2019
SAPTotals           5               Opening_Balance     39          4588.22
SAPTotals           6               Closing_Balance     39          7777.25
SAPTotals           7               Total_Credits       39          999
SAPTotals           8               Total_Debits        39          2012
SAPTotals           9               Impex_No            39          I907
SAPTotals           10              Export_Date         39

This is the result I get:

Batch_Reference Bank_Account Sequence_No Entry_Date Opening_Balance Closing_Balance Total_Credits  Total_Debits Impex_No Export_Date
39              NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     NULL
35              NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     NULL
36              NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     NULL
37              NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     NULL
38              NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            37           NULL        NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            39           NULL        NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         35          NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         36          NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         37          NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         38          NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         39          NULL       NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        35         NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        38         NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        39         NULL            NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       38              NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       37              NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       35              NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       36              NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       39              NULL            NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            36              NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            35              NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            37              NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            38              NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            39              NULL           NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            36             NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            37             NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            38             NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            35             NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            39             NULL         NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            NULL           39           NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            NULL           35           NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            NULL           36           NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            NULL           37           NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            NULL           38           NULL     NULL
NULL            NULL         NULL        NULL       NULL            NULL            NULL           NULL         39       NULL
NULL            NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     39
NULL            NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     35
NULL            NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     36
NULL            NULL         NULL        NULL       NULL            NULL            NULL           NULL         NULL     38

This is the result set i want:

GroupingID Batch_Reference Bank_Account Sequence_Number Entry_Date Opening_Balance Closing_Balance Total_Credits Total_Debits Impex_No Export_Date
35         BATCH001        72368837     123             29/01/2019 2458.65         2587.99         76            34           I907     01/02/2019
36         BATCH002        72368837     124             30/01/2019 2587.99         2308.32         154           776          I907     02/02/2019
37         BATCH003        72368837     125             31/01/2019 2308.32         4219.37         345           87           I907     NULL
38         BATCH566        73297699     344             30/01/2019 1987.66         4588.22         67            98           I907     04/02/2019
39         BATCH_ABC       73297699     345             31/01/2019 4588.22         7777.25         999           2012         I907     NULL

Here is my SQL:

select  
    pvt.Batch_Reference,
    pvt.Bank_Account,
    pvt.Sequence_No,
    pvt.Entry_Date,
    pvt.Opening_Balance,
    pvt.Closing_Balance,
    pvt.Total_Credits,
    pvt.Total_Debits,
    pvt.Impex_No,
    pvt.Export_Date 
from    (
    SELECT * FROM CustomConfigItems
    ) as t
    Pivot 
    (
        Max(GroupingID) 
        FOR ColumnName 
        IN
        (
            [Batch_Reference],
            [Bank_Account],
            [Sequence_No],
            [Entry_Date],
            [Opening_Balance],
            [Closing_Balance],
            [Total_Credits],
            [Total_Debits],
            [Impex_No],
            [Export_Date]
        )
    ) pvt

Whatever I try I cannot get the desired results. I tried to follow the response to this posting: Pivot table returns multiple rows with NULL, results should be grouped on one row

But just cannot get the results I need :-(

Apologies for 1st attempt to post this, should be neater now...

Where is the query? Which database are you using? MySQL doesn't have a PIVOT operation — Panagiotis Kanavos, Mar 01 '19 at 15:30
Sorry I was trying to edit the post (only just posted this) - editing now... wow you guys are brutal! — CHale, Mar 01 '19 at 15:55
Apologies this was my 1st post ever. I thought I'd get to preview the post before submission, hence wasn't complete. Shame i got a -1 already - quite annoying. And now it looks like it didn't paste the sql neatly either... — CHale, Mar 01 '19 at 15:58

Tim Mylott · Accepted Answer · 2019-03-01T19:02:55.870

Small tweak to what you already have will get you there.

Change Max(GroupingID) to Max(Value) as that's what you want to pivot on.

Then either do a MAX on all the pivoted fields and group by GroupingId or use a sub query to only return GroupID, ColumnName, Value.

Here's a working example:

DECLARE @CustomConfigItems TABLE
    (
        [CustomConfigname] NVARCHAR(100)
      , [ColumnNumber] INT
      , [ColumnName] NVARCHAR(100)
      , [GroupingID] INT
      , [Value] NVARCHAR(100)
    );

INSERT INTO @CustomConfigItems (
                                   [CustomConfigname]
                                 , [ColumnNumber]
                                 , [ColumnName]
                                 , [GroupingID]
                                 , [Value]
                               )
VALUES ( 'SAPTotals', '1', 'Batch_Reference', '35', 'BATCH001' )
     , ( 'SAPTotals', '2', 'Bank_Account', '35', '72368837' )
     , ( 'SAPTotals', '3', 'Sequence_No', '35', '123' )
     , ( 'SAPTotals', '4', 'Entry_Date', '35', '29/01/2019' )
     , ( 'SAPTotals', '5', 'Opening_Balance', '35', '2458.65' )
     , ( 'SAPTotals', '6', 'Closing_Balance', '35', '2587.99' )
     , ( 'SAPTotals', '7', 'Total_Credits', '35', '76' )
     , ( 'SAPTotals', '8', 'Total_Debits', '35', '34' )
     , ( 'SAPTotals', '9', 'Impex_No', '35', 'I907' )
     , ( 'SAPTotals', '10', 'Export_Date', '35', '1/2/2019' )
     , ( 'SAPTotals', '1', 'Batch_Reference', '36', 'BATCH002' )
     , ( 'SAPTotals', '2', 'Bank_Account', '36', '72368837' )
     , ( 'SAPTotals', '3', 'Sequence_No', '36', '124' )
     , ( 'SAPTotals', '4', 'Entry_Date', '36', '30/01/2019' )
     , ( 'SAPTotals', '5', 'Opening_Balance', '36', '2587.99' )
     , ( 'SAPTotals', '6', 'Closing_Balance', '36', '2308.32' )
     , ( 'SAPTotals', '7', 'Total_Credits', '36', '154' )
     , ( 'SAPTotals', '8', 'Total_Debits', '36', '776' )
     , ( 'SAPTotals', '9', 'Impex_No', '36', 'I907' )
     , ( 'SAPTotals', '10', 'Export_Date', '36', '2/2/2019' )
     , ( 'SAPTotals', '1', 'Batch_Reference', '37', 'BATCH003' )
     , ( 'SAPTotals', '2', 'Bank_Account', '37', '72368837' )
     , ( 'SAPTotals', '3', 'Sequence_No', '37', '125' )
     , ( 'SAPTotals', '4', 'Entry_Date', '37', '31/01/2019' )
     , ( 'SAPTotals', '5', 'Opening_Balance', '37', '2308.32' )
     , ( 'SAPTotals', '6', 'Closing_Balance', '37', '4219.37' )
     , ( 'SAPTotals', '7', 'Total_Credits', '37', '345' )
     , ( 'SAPTotals', '8', 'Total_Debits', '37', '87' )
     , ( 'SAPTotals', '9', 'Impex_No', '37', 'I907' )
     , ( 'SAPTotals', '10', 'Export_Date', '37', '' )
     , ( 'SAPTotals', '1', 'Batch_Reference', '38', 'BATCH566' )
     , ( 'SAPTotals', '2', 'Bank_Account', '38', '73297699' )
     , ( 'SAPTotals', '3', 'Sequence_No', '38', '344' )
     , ( 'SAPTotals', '4', 'Entry_Date', '38', '30/01/2019' )
     , ( 'SAPTotals', '5', 'Opening_Balance', '38', '1987.66' )
     , ( 'SAPTotals', '6', 'Closing_Balance', '38', '4588.22' )
     , ( 'SAPTotals', '7', 'Total_Credits', '38', '67' )
     , ( 'SAPTotals', '8', 'Total_Debits', '38', '98' )
     , ( 'SAPTotals', '9', 'Impex_No', '38', 'I907' )
     , ( 'SAPTotals', '10', 'Export_Date', '38', '4/2/2019' )
     , ( 'SAPTotals', '1', 'Batch_Reference', '39', 'BATCH_ABC' )
     , ( 'SAPTotals', '2', 'Bank_Account', '39', '73297699' )
     , ( 'SAPTotals', '3', 'Sequence_No', '39', '345' )
     , ( 'SAPTotals', '4', 'Entry_Date', '39', '31/01/2019' )
     , ( 'SAPTotals', '5', 'Opening_Balance', '39', '4588.22' )
     , ( 'SAPTotals', '6', 'Closing_Balance', '39', '7777.25' )
     , ( 'SAPTotals', '7', 'Total_Credits', '39', '999' )
     , ( 'SAPTotals', '8', 'Total_Debits', '39', '2012' )
     , ( 'SAPTotals', '9', 'Impex_No', '39', 'I907' )
     , ( 'SAPTotals', '10', 'Export_Date', '39', '' );

SELECT [GroupingID]
       , MAX([pvt].[Batch_Reference]) AS [Batch_Reference]
       , MAX([pvt].[Bank_Account]) AS [Bank_Account]
       , MAX([pvt].[Sequence_No]) AS [Sequence_No]
       , MAX([pvt].[Entry_Date]) AS [Entry_Date]
       , MAX([pvt].[Opening_Balance]) AS [OpeningBalane]
       , MAX([pvt].[Closing_Balance]) AS [ClosingBalance]
       , MAX([pvt].[Total_Credits]) AS [Total_Credits]
       , MAX([pvt].[Total_Debits]) AS [Total_Debits]
       , MAX([pvt].[Impex_No]) AS [Impex_No]
       , MAX([pvt].[Export_Date]) AS [Export_Date]
     FROM   @CustomConfigItems
         PIVOT (
                   MAX([Value]) --Pivot on this column
                   FOR [ColumnName] ----Make column where [Value] is in one of these
                   IN ( [Batch_Reference]
                                        , [Bank_Account]
                                        , [Sequence_No]
                                        , [Entry_Date]
                                        , [Opening_Balance]
                                        , [Closing_Balance]
                                        , [Total_Credits]
                                        , [Total_Debits]
                                        , [Impex_No]
                                       , [Export_Date]
                                       )
               ) [pvt]
               GROUP BY [pvt].[GroupingID]

--or to simplify and not use all the aggregate functions
--use a sub query to only get GroupingID, Column and Value
SELECT *
FROM   (
           SELECT [GroupingID]
                , [ColumnName]
                , [Value]
           FROM   @CustomConfigItems
       ) AS [t]
PIVOT (
          MAX([Value]) --Pivot on this column
          FOR [ColumnName] ----Make column where [Value] is in one of these
          IN ( [Batch_Reference], [Bank_Account], [Sequence_No], [Entry_Date], [Opening_Balance], [Closing_Balance], [Total_Credits], [Total_Debits], [Impex_No], [Export_Date] )
      ) [pvt];

Thanks Tim this is fantastic. I've gone with the latter. Your in-line comments are like a breath of fresh air too - so hard to get any good explanation elsewhere - especially when you're under pressure! I'd mark as the answer, but can't see how. — CHale, Mar 04 '19 at 09:05

Pivot table returns multiple rows with NULLs, are not grouped on one row

1 Answers1

Linked

Related