Home > DeveloperSection > Forums > Pivot table in SQL Server 2008
C G E Mannerheim
C G E Mannerheim

Total Post:29

Points:203
Posted on    September-28-2013 8:08 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 946  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'


Ida B Wells
Ida B Wells

Total Post:30

Points:210
Posted on    September-28-2013 9:03 AM

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

Don't want to miss updates? Please click the below button!

Follow MindStick