I have the following table with two fields as shown below:
I want to show the pivot table of the following data into verticle form.
Table: test_10
create table test_10
(
col1 varchar(10),
col2 varchar(10)
);
Inserting records:
insert into test_10 values('A','2015-01-01'),('A','2015-01-05'),('A','2015-01-10'),('A','2015-02-15'),
('B','2015-01-01'),('B','2015-01-05'),('B','2015-01-10'),('B','2015-02-15'),
('C','2015-02-02'),('C','2015-02-05'),('C','2015-02-08'),('C','2015-02-16');
Expected Result:
ColX Jan Feb
-------------------
A 3 1
B 3 1
C 0 4
My try:
SELECT ColX,SUM(Jan) as Jan,SUM(Feb) as after
from
(
SELECT col1 as ColX,col1,
(CASE WHEN col2 BETWEEN convert(date,'01-01-2015',105) AND convert(date,'31-01-2015',105) THEN count(col1) ELSE 0 END) as Jan,
(CASE WHEN col2 BETWEEN convert(date,'01-02-2015',105) AND convert(date,'28-02-2015',105) THEN count(col1) ELSE 0 end) as Feb
from test_10
Group By col1,col2
) a
Pivot
(
COUNT(col1)
FOR col1 in([A],[B],[C])
)as pvt
GROUP BY ColX;
But Getting result:
ColX Jan Feb
-------------------
A 1 1
B 1 1
C 0 1
No need of pivot, try conditional aggregation :
SELECT col1,
SUM(CASE
WHEN col2 BETWEEN CONVERT(DATE, '01-01-2015', 105) AND CONVERT(DATE, '31-01-2015', 105) THEN 1
ELSE 0
END) AS Jan,
SUM(CASE
WHEN col2 BETWEEN CONVERT(DATE, '01-02-2015', 105) AND CONVERT(DATE, '28-02-2015', 105) THEN 1
ELSE 0
END) AS Feb
FROM test_10
GROUP BY col1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments