PIVOT TABLE IN SQL SERVER 2008

C G E Mannerheim

Total Post:28

Points:196
Posted by  C G E Mannerheim
 1346  View(s)
Ratings:
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. Ida B Wells

    Post:30

    Points:210
    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
    VALUES
        ('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 (
        COUNT(scandate)
        FOR filestatus IN ([Allocation Pending], [Coding Pending])
    ) a




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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!