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'

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

forum

Pivot table in SQL Server 2008

Anonymous User

Can you answer this question?

1 Answers

Liked By