if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[weatherreport]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[weatherreport]
GO
CREATE TABLE [dbo].[weatherreport] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[city] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[reportdate] [datetime] NULL ,
[temperature] [decimal](18, 1) NULL
) ON [PRIMARY]
GO
declare @s varchar(8000)
set @s='select reportdate '
select @s=@s+',['+city+']=max(case city when '''+city+''' then temperature else 0 end)'
from weatherreport
group by city
set @s=@s+' from weatherreport group by reportdate'
exec(@s)
select reportdate ,[广州]=max(case city when '广州' then temperature else 0 end),
[阳光]=max(case city when '阳光' then temperature else 0 end),
[湛江]=max(case city when '湛江' then temperature else 0 end)
from weatherreport group by reportdate
select * from weatherreport
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetIDTableByIDList]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetIDTableByIDList]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION GetIDTableByIDList( @IDList nvarchar(4000),@SPLITCHAR nvarchar(50))
returns @T table(ID varchar(20))
as
begin
/*
declare @IDList nvarchar(4000)
set @IDList='canip,ok,l'
@SPLITCHAR nvarchar(50),
select * from dbo.IDTable(@IDList)
*/
declare @ID NVARCHAR(50)
if(@SPLITCHAR='')
set @SPLITCHAR=','
if(CHARINDEX(@SPLITCHAR,@IDList)>0)
begin
DECLARE @L INT -- 第一个分隔字符的位置
DECLARE @S INT -- 第二个分隔字符的位置
SET @L = 0
SET @S = CHARINDEX(@SPLITCHAR, @IDList, @L)
WHILE @L <= LEN(@IDList)
BEGIN
IF @S = 0 SET @S = LEN(@IDList) + 1 -- 如果到最后一个字符串那么第二个分隔字符的位置就是这个字符串的长度加一
SET @ID = SUBSTRING(@IDList, @L, @S - @L) -- 取值
SET @L = @S + 1
SET @S = CHARINDEX(@SPLITCHAR, @IDList, @L)
IF LTRIM(RTRIM(@ID)) = '' CONTINUE -- 如果是空字符串就跳过
if(not exists(select [ID] from @T where [id]=@ID))
insert into @T([ID])select @ID
--print @ID
End
end
else
insert into @T([ID])select @IDList
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------------------------------------
-- 功能: 将二进制字符串(VARCHAR)转换为十进制数(INT)
-- 输入参数: @StrBin 待转换的二进制字符串
-- 返回值: 相应的十进制数,如果二进制数非法,返回-1
-- 注意: @StrBin的长度不能大于31
-- 例子: SELECT dbo.Bin2Dec('1001') AS '15的二进制表示'
CREATE FUNCTION Bin2Dec(@StrBin VARCHAR(31))
RETURNS INT ---- 只支持最多31位长二进制字符串的解析
AS
BEGIN
DECLARE @DecValue AS INT -- 十进制值
DECLARE @BinLen AS TINYINT -- 二进制字符串长度
DECLARE @Index AS TINYINT -- 处理二进制字符串长度的索引
DECLARE @CurrBit AS CHAR(1) -- 当前在处理哪一位
SET @BinLen = LEN(@StrBin)
SET @DecValue = 0
SET @Index = 0
WHILE @Index < @BinLen
BEGIN
SET @Index = @Index + 1
SET @CurrBit = CAST(SUBSTRING(@StrBin, @Index, 1) AS CHAR(1))
IF (@CurrBit <> '0' AND @CurrBit <> '1') -- 出现非法字符,返回-1
BEGIN
SET @DecValue = -1
BREAK
END
ELSE
BEGIN
SET @DecValue = @DecValue * 2
IF(@CurrBit = '1')
BEGIN
SET @DecValue = @DecValue + POWER(2, 0)
END
END
END
RETURN @DecValue
END
group by with roll up /cube