SQL Server Pivot Table with multiple null and not in the correct position

Question

Please someone help me.. this is my table

ACCT_NR   DEDNCODE  DEDN_ALPHA             AMOUNT AGING PAY_PERIOD TYPE
20110101401 159      MEM PLAN               16.00   0   012013       S
20110101401 557      PHILHEALTH             175.00  0   012013       S
20110101401 617      S G T I                6.00    0   012013       S
20110101401 634      PAGIBIG C              100.00  0   012013       S
20110101401 761      PSMBFI EP              445.02  0   012013       S
20110101401 762      PSMBFI BG              25.00   0   012013       S
20110101401 009      PSSLAI                 2000.00 0   012013       N
20110101401 350      W TAX                  786.80  0   012013       N
20110101401 551      AFPMBA ADDL            1054.90 0   012013       N
20110101401 768      PSMBFI EL              3277.78 7   012013       N

this is my query to achieve this.

SELECT [ACCT_NR]
      ,[DEDNCODE]
      ,[DEDN_ALPHA]
      ,[AMOUNT]
      ,[AGING]
      ,[PAY_PERIOD]
      ,[TYPE]
  FROM [IFIS_EarnDeduction].[dbo].[NDEDUCTIONS2013] where ACCT_NR = '20110101401' and PAY_PERIOD = '012013'

what i want the result is this

MEMPLAN PHILHEALTH SGTI PAGIBIGC PSMBFIEP PSMBFIBG PSSLAI   WTAX   AFPMBAADDL PSMBFIEL
 16.00   175.00    6.00  100.00   445.02   25.00   200.00   786.80  1054.90    3277.78

i use this code to generate what i want..

DECLARE @cols AS NVARCHAR(MAX),
    @acctnr NVARCHAR(MAX),
    @payperiod NVARCHAR(MAX),
    @payperiod2 NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
    SET @acctnr = '20110101401';
    SET @payperiod = '012013';
    SET @payperiod2= '062013';

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.DEDN_ALPHA) 
                    from [IFIS_EarnDeduction].[dbo].[NDEDUCTIONS2013] p
                   where ACCT_NR = '20110101401' and PAY_PERIOD = '012013'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
                SELECT *
  FROM [IFIS_EarnDeduction].[dbo].[NDEDUCTIONS2013]
  where ACCT_NR = Cast((' + @acctnr + ') As nvarchar(30))
            ) x
            pivot 
            (
                min(amount)
                for DEDN_ALPHA in (' + @cols + ')
            ) p '

execute(@query)

but the result is this.. so many nulls and the data is not in the right place

MEMPLAN PHILHEALTH SGTI PAGIBIGC PSMBFIEP PSMBFIBG PSSLAI   WTAX   AFPMBAADDL PSMBFIEL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    2000.00 NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    2000.00 NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    2000.00 NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    2000.00 NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    2000.00 NULL    NULL
NULL    16.00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    16.00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    16.00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    16.00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    16.00   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    786.80
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    786.80
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    786.80
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    786.80
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    786.80
1054.90 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
1054.90 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
1054.90 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
1054.90 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
1054.90 NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    175.00  NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    175.00  NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    175.00  NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    175.00  NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    175.00  NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    6.00    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    6.00    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    6.00    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    6.00    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    6.00    NULL
NULL    NULL    100.00  NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    100.00  NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    100.00  NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    100.00  NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    100.00  NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    445.02  NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    445.02  NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    445.02  NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    445.02  NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    445.02  NULL    NULL    NULL
NULL    NULL    NULL    NULL    25.00   NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    25.00   NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    25.00   NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    25.00   NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    25.00   NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    3277.78 NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    3277.78 NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    3277.78 NULL    NULL    NULL    NULL
NULL    NULL    NULL    NULL    NULL    3277.78 NULL    NULL    NULL    NULL

Can someone please advice me how to achieve my goal? thanks in advance..

Answer 1

As a supplement to Devart's answer (which is correct, though in my mind incomplete), the reason this was happening is because when you PIVOT, the engine uses ALL the columns from the source rowset.

One way to think about it is that every column not consumed by the PIVOT (both the aggregated column and the value column are removed and replaced with the pivoted columns) is implicitly given a GROUP BY. Your query included columns that made each row unique based on this grouping, such as DEDNCODE and TYPE. You already had the correct form of query--where the source data is inside derived table x--but it simply had too many columns. The solution, then, is as simple as only selecting the columns that are absolutely needed by the PIVOT. In the case of your current query, you don't want any extra columns, so you select just the desired two with SELECT AMOUNT, DEDN_ALPHA FROM ... inside the derived table.

You can change 'SELECT ' + @cols + ' from ... ' to 'SELECT * from ... ' to see more clearly what was going wrong with your query. Notice that PIVOT replaces ALL tables that come before it in the FROM clause! You can no longer address any columns from prior tables--try it by using x.* and you'll see that only p.* exists now. For the record, you can actually continue to JOIN to additional tables, or even perform another PIVOT--just keep in mind that each PIVOT consumes ALL prior tables and aliases in the FROM clause, making them inaccessible from that point on--you use the most recent PIVOT alias to access those columns.

Answer 2

You just need change this code -

SELECT *
FROM [IFIS_EarnDeduction].[dbo].[NDEDUCTIONS2013]

to this -

SELECT AMOUNT, DEDN_ALPHA
FROM [IFIS_EarnDeduction].[dbo].[NDEDUCTIONS2013]

Or try this -

DDL:

IF OBJECT_ID (N'tempdb.dbo.#temp') IS NOT NULL
   DROP TABLE #temp

CREATE TABLE #temp
(
       ACCT_NR VARCHAR(30)
     , DEDNCODE VARCHAR(30)
     , DEDN_ALPHA VARCHAR(12)
     , AMOUNT DECIMAL(10,2)
     , AGING INT
     , PAY_PERIOD CHAR(6)
     , [TYPE] CHAR(1)
)

INSERT INTO #temp (ACCT_NR, DEDNCODE, DEDN_ALPHA, AMOUNT, AGING, PAY_PERIOD, [TYPE])
VALUES 
     ('20110101401', '159', 'MEM PLAN', 16.00  , 0, '012013', 'S'),
     ('20110101401', '557', 'PHILHEALTH', 175.00 , 0, '012013', 'S'),
     ('20110101401', '617', 'S G T I', 6.00   , 0, '012013', 'S'),
     ('20110101401', '634', 'PAGIBIG C', 100.00 , 0, '012013', 'S'),
     ('20110101401', '761', 'PSMBFI EP', 445.02 , 0, '012013', 'S'),
     ('20110101401', '762', 'PSMBFI BG', 25.00  , 0, '012013', 'S'),
     ('20110101401', '009', 'PSSLAI', 2000.00, 0, '012013', 'N'),
     ('20110101401', '350', 'W TAX', 786.80 , 0, '012013', 'N'),
     ('20110101401', '551', 'AFPMBA ADDL', 1054.90, 0, '012013', 'N'),
     ('20110101401', '768', 'PSMBFI EL', 3277.78, 7, '012013', 'N')

Query:

DECLARE 
       @acctnr NVARCHAR(30)
     , @payperiod NVARCHAR(30)
     , @SQL NVARCHAR(MAX)

SELECT 
       @acctnr = '20110101401'
     , @payperiod = '012013'

SELECT @SQL = '
     SELECT *
     FROM (
          SELECT AMOUNT, DEDN_ALPHA
          FROM #temp
          WHERE ACCT_NR = CAST(' + @acctnr + ' AS NVARCHAR(30))
     ) x
     PIVOT (
          MIN(AMOUNT)
          FOR DEDN_ALPHA IN (' + cols + ')
     ) p'
FROM (
     SELECT cols = STUFF((
          SELECT ', [' + p.DEDN_ALPHA + ']' 
          FROM (
               SELECT DISTINCT 
                      p.DEDN_ALPHA
                    , rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
               FROM #temp p
               WHERE ACCT_NR = @acctnr
                    AND PAY_PERIOD = @payperiod
          ) p
          ORDER BY rn
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
) t

EXEC sys.sp_executesql @SQL

Results: