Advertise with us

Pivot table in SQL Server 2008

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'

Last updated:9/28/2013 9:03:28 AM

1 Answers

Ida B Wells
Ida B Wells

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