62,074
社区成员
发帖
与我相关
我的任务
分享
declare @sql varchar(max),@str varchar(max)
set @str=stuff(replace(replace((select distinct Date from table for xml auto)
,'<table Date="',','),'"/>',''),1,1,'')
set @sql ='select Date,'+ @str+' from table pivot (max(Category) for Date in ('+@str +'))as pvt'
exec(@sql)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Uinatlex_Split]
(
@str VARCHAR(1024),
@split VARCHAR(10),
@index INT
)
RETURNS VARCHAR(1024)
AS
BEGIN
DECLARE @location INT
DECLARE @start INT
DECLARE @next INT
DECLARE @seed INT
SET @str=LTRIM(RTRIM(@str))
SET @start=1
SET @next=1
SET @seed=LEN(@split)
SET @location=CHARINDEX(@split,@str)
WHILE @location<>0 and @index>@next
BEGIN
SET @start=@location+@seed
SET @location=CHARINDEX(@split,@str,@start)
SET @next=@next+1
END
IF @location =0 SELECT @location =LEN(@str)+1
RETURN SUBSTRING(@str,@start,@location-@start)
END
SELECT Date AS 日期,dbo.Uinatlex_Split(Category,'|',0) AS 收入,dbo.Uinatlex_Split(Category,'|',1) AS 支出 FROM TABLE
SELECT CONVERT(varchar(100),Date, 23) AS 日期,
SUM(CASE WHEN Category='收入' THEN MyMoney END) AS 收入,
SUM(CASE WHEN Category='支出' THEN MyMoney END) AS 支出
FROM table_name
GROUP BY CONVERT(varchar(100),Date, 23)
SELECT CONVERT(varchar(100),Date, 23),
SUM(CASE WHEN Category='收入' THEN MyMoney END),
SUM(CASE WHEN Category='支出' THEN MyMoney END)
FROM table_name
GROUP BY CONVERT(varchar(100),Date, 23)