Pivot table in SQL Server 2008

Total Post:28


 1597  View(s)
Rate this:

Please help me out in SQL server PIVOT Table. I got the output like below. Now I want the total count of allocation pending and coding pending in separate columns under each date row.

select ScanDate, filestatus, COUNT(filestatus) as filecount from ScanLog 

where FileSource = 'ebridge'
group by filestatus, ScanDate
scandate filestatus filecount
2013-08-01 Allocation Pending 8
2013-08-01 Coding Pending 1
2013-08-02 Allocation Pending 4
2013-08-02 Coding Pending 1
2013-08-03 Allocation Pending 4
2013-08-04 Allocation Pending 18
2013-08-04 Coding Pending 3
2013-08-05 Allocation Pending 6

I used the following code but got error as 'scandate' is not a valid field. Please guide me.

select [scandate] from ScanLog 

pivot (count(scandate)
for filestatus in ([allocation pending],[coding pending])) as A
where FileSource = 'ebridge'

  1. Re: Pivot table in SQL Server 2008

    Hey Mannerheim!

    Try this one -

    DECLARE @temp TABLE (
          ScanDate DATETIME
        , FileSource VARCHAR(10)
        , FileStatus VARCHAR(30)
        , FileCount INT


    INSERT INTO @temp
        ('2013-08-01', 'ebridge', 'Allocation Pending', 8),
        ('2013-08-01', 'ebridge', 'Coding Pending', 1),
        ('2013-08-02', 'ebridge', 'Allocation Pending', 4),
        ('2013-08-02', 'ebridge', 'Coding Pending', 1),
        ('2013-08-03', 'ebridge', 'Allocation Pending', 4),
        ('2013-08-04', 'ebridge', 'Allocation Pending', 18),
        ('2013-08-04', 'ebridge', 'Coding Pending', 3),
        ('2013-08-05', 'ebridge', 'Allocation Pending', 6)

    SELECT *
    FROM (
        SELECT scandate, filestatus
        FROM @temp
        WHERE FileSource = 'ebridge'
    ) t
    PIVOT (
        FOR filestatus IN ([Allocation Pending], [Coding Pending])
    ) a

      Modified On Mar-31-2018 04:03:28 AM


Please check, If you want to make this post sponsored

You are not a Sponsored Member. Click Here to Subscribe the Membership.