declare @T table(Device varchar(10),Data int,Times varchar(20))
insert into @T select 1,50,'Monday'
union all select 2,80,'Tuesday'
union all select 1,85,'Wednesday'
union all select 2,90,'Wednesday'
select Times,DeviceOneData=sum(case when Device=1 then data else null end),
DeviceTwoData=sum(case when Device=2 then data else null end)
from @T group by Times
insert into @T select 1,50,'Monday'
union all select 2,80,'Tuesday'
union all select 1,85,'Wednesday'
union all select 2,90,'Wednesday'
select Times,DeviceOneData=sum(case when Device=1 then data else null end),
DeviceTwoData=sum(case when Device=2 then data else null end)
from @T group by Times
insert into @t select 1,50,'Monday'
insert into @t select 2,80,'Tuesday'
insert into @t select 1,85,'Wednesday'
insert into @t select 2,90,'Wednesday'
select distinct time,(select data from @t where time=a.time and device=1) DeviceOneData,
(select data from @t where time=a.time and device=2) DeviceTwoData
from @t a
这时间都可以生孩子了。。
A.Data as DeviceOneData ,
B.Data as DeviceTwoData
from (select * from Device_Data where Device=1) A
full join (select * from Device_Data where Device=2) B
on A.Time=B.Time
declare @t table (device int,data int,time varchar(10))
insert into @t select 1,50,'Monday'
insert into @t select 2,80,'Tuesday'
insert into @t select 1,85,'Wednesday'
insert into @t select 2,90,'Wednesday'select isnull(A.Time,B.Time) as Time,
A.Data as DeviceOneData ,
B.Data as DeviceTwoData
from (select * from @t where Device=1) A
full join (select * from @t where Device=2) B
on A.Time=B.Time
order by Time/*Time DeviceOneData DeviceTwoData
---------- ------------- -------------
Monday 50 NULL
Tuesday NULL 80
Wednesday 85 90
*/
CREATE TABLE T
(
Device INT,
Data INT,
Time VARCHAR(20)
)INSERT INTO T
SELECT 1,50,'Monday' UNION ALL
SELECT 2,80,'Tuesday' UNION ALL
SELECT 1,85,'Wednesday' UNION ALL
SELECT 2,90,'Wednesday'
GO --创建数字转英文的函数
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_num_eng]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_num_eng]
GO
CREATE FUNCTION [dbo].[f_num_eng] (@num numeric(15,2))
RETURNS varchar(400)
AS
BEGIN
/**************************************************
Number to letters Version 1.0
Copyright (C) pbsql 2004
Language: US. English
***************************************************/
DECLARE @i int,@hundreds int,@tenth int,@one int
DECLARE @thousand int,@million int,@billion int
DECLARE @numbers varchar(400),@s varchar(15),@result varchar(400)
SET @numbers='ONE TWO THREE FOUR FIVE '
+'SIX SEVEN EIGHT NINE TEN '
+'ELEVEN TWELVE THIRTEEN FOURTEEN FIFTEEN '
+'SIXTEEN SEVENTEEN EIGHTEEN NINETEEN '
+'TWENTY THIRTY FORTY FIFTY '
+'SIXTY SEVENTY EIGHTY NINETY '
SET @numbers='ONE TWO THREE FOUR FIVE SIX SEVEN EIGHT NINE '
+'TEN ELEVEN TWELVE THIRTEEN FOURTEEN FIFTEEN SIXTEEN SEVENTEENEIGHTEEN NINETEEN '
SET @numbers=@numbers+'TWENTY THIRTY FORTY FIFTY SIXTY SEVENTYEIGHTY NINETY '
SET @s=RIGHT('000000000000000'+CAST(@num AS varchar(15)),15)
SET @billion=CAST(SUBSTRING(@s,1,3) AS int)--将12位整数分成4段:十亿、百万、千、百十个
SET @million=CAST(SUBSTRING(@s,4,3) AS int)
SET @thousand=CAST(SUBSTRING(@s,7,3) AS int)
SET @result=''
SET @i=0
WHILE @i<=3
BEGIN
SET @hundreds=CAST(SUBSTRING(@s,@i*3+1,1) AS int)--百位0-9
SET @tenth=CAST(SUBSTRING(@s,@i*3+2,1) AS int)
SET @one=(CASE @tenth WHEN 1 THEN 10 ELSE 0 END)+CAST(SUBSTRING(@s,@i*3+3,1) AS int)--个位0-19
SET @tenth=(CASE WHEN @tenth<=1 THEN 0 ELSE @tenth END)--十位0、2-9
IF (@i=1 and @billion>0 and (@million>0 or @thousand>0 or @hundreds>0 or @tenth>0 or @one>0)) or
(@i=2 and (@billion>0 or @million>0) and (@thousand>0 or @hundreds>0 or @tenth>0 or @one>0)) or
(@i=3 and (@billion>0 or @million>0 or @thousand>0) and (@hundreds>0 or @tenth>0 or @one>0))
SET @result=@result+', '--每段之间加连接符,
IF @hundreds>0
SET @result=@result+RTRIM(SUBSTRING(@numbers,@hundreds*10-9,10))+' HUNDRED'
IF @tenth>=2 and @tenth<=9
BEGIN
IF @hundreds>0
SET @result=@result+' AND '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@tenth*10+171,10))
END
IF @one>=1 and @one<=19
BEGIN
IF @tenth>0
SET @result=@result+'-'
ELSE
IF @hundreds>0
SET @result=@result+' AND '
SET @result=@result+RTRIM(SUBSTRING(@numbers,@one*10-9,10))
END
IF @i=0 and @billion>0
SET @result=@result+' BILLION'
IF @i=1 and @million>0
SET @result=@result+' MILLION'
IF @i=2 and @thousand>0
SET @result=@result+' THOUSAND'
SET @i=@i+1
END
IF SUBSTRING(@s,14,2)<>'00'
BEGIN
SET @result=@result+' POINT '
IF SUBSTRING(@s,14,1)='0'
SET @result=@result+'ZERO'
ELSE
SET @result=@result+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,14,1) AS int)*10-9,10))
IF SUBSTRING(@s,15,1)<>'0'
SET @result=@result+' '+RTRIM(SUBSTRING(@numbers,CAST(SUBSTRING(@s,15,1) AS int)*10-9,10))
END
RETURN(@result)
END
GO
--开始执行语句
DECLARE @sql VARCHAR(8000) SELECT @sql = 'SELECT Time'
SELECT @sql = @sql + ',[Device'+LTRIM(RTRIM(DBO.f_num_eng(Device)))+'DATA] = SUM(CASE WHEN Device = '+CAST(Device AS VARCHAR)+' THEN Data END)'
FROM T GROUP BY Device
EXEC(@sql+' FROM T GROUP BY Time')
--删除环境
DROP FUNCTION f_num_eng
DROP TABLE TTime DeviceONEDATA DeviceTWODATA
-------------------- ------------- -------------
Monday 50 NULL
Tuesday NULL 80
Wednesday 85 90警告: 聚合或其它 SET 操作消除了空值。
max(case device when 1 then date else null end) DeviceOneData,
max(case device when 2 then date else null end) DeviceTwoData
from Device_Data
group by time
insert into tb values(1, 50 , 'Monday')
insert into tb values(2, 80 , 'Tuesday')
insert into tb values(1, 85 , 'Wednesday')
insert into tb values(2, 90 , 'Wednesday')
goselect time,
max(case device when 1 then data else null end) DeviceOneData,
max(case device when 2 then data else null end) DeviceTwoData
from tb
group by timedrop table tb/*
time DeviceOneData DeviceTwoData
---------- ------------- -------------
Monday 50 NULL
Tuesday NULL 80
Wednesday 85 90(所影响的行数为 3 行)
*/
,deviceTwoDATA=max(CASE WHEN device=2 THEN data else null end)
from device_data
group by time