行数据列数据互换SQL例子

admin管理员组

文章数量:1441957

行数据列数据互换SQL例子

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

本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。 原始发表：2008-06-20，如有侵权请联系 cloudcommunity@tencent 删除前往查看二进制数据字符串sqlset

本文标签： 行数据列数据互换SQL例子