sql行转列

DROP TABLE test

CREATE TABLE test(
id int PRIMARY KEY IDENTITY,
idate datetime,
result nvarchar(2)
)

INSERT into test VALUES (getdate(),'有')
INSERT into test values('2017-04-18','有')
INSERT into test values('2017-04-18','无')
INSERT into test values('2017-04-19','无')
INSERT into test values('2017-04-18','无')
INSERT into test values('2017-04-19','无')

SELECT CONVERT(nvarchar(10),idate,120) AS Date,case when result='有' then '有' when result='无' then '无' END AS Result FROM test
SELECT CONVERT(nvarchar(10),idate,120)  as '日期',
COUNT(CASE when result='有' then '有'  END) '有',
COUNT(CASE when result='无' then '无' END) '无'
 FROM test GROUP BY CONVERT(nvarchar(10),idate,120)