PIVOT TABLE IN SQL SERVER 2008

C G E Mannerheim

Total Post:29

Points:203
Posted by  C G E Mannerheim
 1186  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

Answer

NEWSLETTER

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