T-SQL 教程-2

用的次数不是很多，但是每次都会让我纠结一会的行转列，列转行，有了这个示例，就可以轻松应对这类问题了：

 

View Code
 --行转列
 create table #CarLog(LogDate datetime,PathLine nvarchar(10),Amount int)
 --drop table #CarLog
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','1号线',1)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-25','1号线',91)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-26','1号线',66)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',44)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','3号线',33)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','5号线',12)
 insert into #CarLog(LogDate,PathLine,Amount)VALUES('2011-12-24','6号线',22)
 
 select * from #CarLog
 
 --方法1
 SELECT LogDate,
 (CASE WHEN PathLine='1号线' THEN Amount ELSE 0 END)AS '1号线',
 (CASE WHEN PathLine='2号线' THEN Amount ELSE 0 END)AS '2号线',
 (CASE WHEN PathLine='3号线' THEN Amount ELSE 0 END)AS '3号线',
 (CASE WHEN PathLine='4号线' THEN Amount ELSE 0 END)AS '4号线',
 (CASE WHEN PathLine='5号线' THEN Amount ELSE 0 END)AS '5号线',
 (CASE WHEN PathLine='6号线' THEN Amount ELSE 0 END)AS '6号线'
 FROM #CarLog
 Group By PathLine,LogDate,Amount
 
 
 
 --方法2
 declare @Str nvarchar(max)
 set @str='select [LogDate]'
 select @str=@str+',['+PathLine+']' from #CarLog group by PathLine
 --exec @str
 print @str
 set @str=@str+' FROM (
  SELECT LogDate,Amount,PathLine
  FROM #CarLog ) AS c
 PIVOT ( sum(Amount) FOR PathLine IN
   ('
 select @str=@str+'['+PathLine+'],' from #CarLog group by PathLine
 
 set @str=left(@str,Len(@str)-1)
 set @str=@str+ ')) AS thePivot
 ORDER BY LogDate'
 print @str
 
 declare @Table table(Logdate datetime,1号线 int, 3号线 int,4号线 int,5号线 int,6号线 int)
 INSERT INTO @Table exec(@str)
 SELECT * FROM @Table
 --drop table #T
 
 
 
 
 select [LogDate],[1号线],[3号线],[5号线],[6号线] FROM (
  SELECT LogDate,Amount,PathLine
  FROM #CarLog ) AS cl
 PIVOT ( sum(Amount) FOR PathLine IN
   ([1号线],[3号线],[5号线],[6号线])) AS thePivot
 ORDER BY LogDate
 ----------------------------------分割线-----------------------------------------------------
 CREATE  TABLE [#StudentScores]
 (
     [UserName]         NVARCHAR(20),        --学生姓名
     [Subject]          NVARCHAR(30),        --科目
     [Score]            FLOAT,               --成绩
 )
 
 INSERT INTO [#StudentScores] SELECT 'Nick', '语文', 81
 INSERT INTO [#StudentScores] SELECT 'Nick', '数学', 91
 INSERT INTO [#StudentScores] SELECT 'Nick', '英语', 72
 INSERT INTO [#StudentScores] SELECT 'Nick', '生物', 83
 INSERT INTO [#StudentScores] SELECT 'Kent', '语文', 84
 INSERT INTO [#StudentScores] SELECT 'Kent', '数学', 99
 INSERT INTO [#StudentScores] SELECT 'Kent', '英语', 77
 INSERT INTO [#StudentScores] SELECT 'Kent', '生物', 79
 --drop table #StudentScores
 ---------------------------
 select * from #StudentScores
 
 SELECT
       UserName,
       MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
       MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
       MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
       MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
 FROM dbo.[#StudentScores]
 GROUP BY UserName
 
 -----------------------------分割线----------------------------------------------------
 CREATE TABLE [#Inpours]
 (
     [ID]                [int] IDENTITY(1,1),
     [UserName]          NVARCHAR(20),  --游戏玩家
     [CreateTime]        DATETIME,      --充值时间   
     [PayType]            NVARCHAR(20),  --充值类型   
     [Money]             DECIMAL,       --充值金额
     [IsSuccess]         BIT,           --是否成功 1表示成功， 0表示失败
     CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID)
 )
 INSERT INTO [#Inpours] SELECT '张三', '2010-05-01', '支付宝', 50, 1
 INSERT INTO [#Inpours] SELECT '张三', '2010-06-14', '支付宝', 50, 1
 INSERT INTO [#Inpours] SELECT '张三', '2010-06-14', '手机短信', 100, 1
 INSERT INTO [#Inpours] SELECT '李四', '2010-06-14', '手机短信', 100, 1
 INSERT INTO [#Inpours] SELECT '李四', '2010-07-14', '支付宝', 100, 1
 INSERT INTO [#Inpours] SELECT '王五', '2010-07-14', '工商银行卡', 100, 1
 INSERT INTO [#Inpours] SELECT '赵六', '2010-07-14', '建设银行卡', 100, 1
 
 select * from #Inpours
 
 SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
        CASE PayType WHEN '支付宝'      THEN SUM(Money) ELSE 0 END AS '支付宝',
        CASE PayType WHEN '手机短信'    THEN SUM(Money) ELSE 0 END AS '手机短信',
        CASE PayType WHEN '工商银行卡'  THEN SUM(Money) ELSE 0 END AS '工商银行卡',
        CASE PayType WHEN '建设银行卡'  THEN SUM(Money) ELSE 0 END AS '建设银行卡'
 FROM #Inpours
 GROUP BY CreateTime, PayType
 
 
 ------
 SELECT
        CreateTime,
        ISNULL(SUM([支付宝]), 0) AS [支付宝],
        ISNULL(SUM([手机短信]), 0) AS [手机短信],
        ISNULL(SUM([工商银行卡]), 0) AS [工商银行卡],
        ISNULL(SUM([建设银行卡]), 0) AS [建设银行卡]
 FROM
 (
     SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,
            CASE PayType WHEN '支付宝'     THEN SUM(Money) ELSE 0 END AS '支付宝',
            CASE PayType WHEN '手机短信'   THEN SUM(Money) ELSE 0 END AS '手机短信',
            CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡',
            CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建设银行卡'
     FROM #Inpours
     GROUP BY CreateTime, PayType
 ) T
 GROUP BY CreateTime
 ------
 DECLARE @cmdText    VARCHAR(8000);
 DECLARE @tmpSql        VARCHAR(8000);
 
 SET @cmdText = 'SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,' + CHAR(10);
 print @cmdText
 SELECT @cmdText = @cmdText + ' CASE PayType WHEN ''' + PayType + ''' THEN SUM(Money) ELSE 0 END AS ''' + PayType
                 + ''',' + CHAR(10)  FROM (SELECT DISTINCT PayType FROM #Inpours ) T
 print @cmdText
 SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)
 
 SET @cmdText = @cmdText + ' FROM #Inpours     GROUP BY CreateTime, PayType ';
 print @cmdText
 SET @tmpSql ='SELECT CreateTime,' + CHAR(10);
 SELECT @tmpSql = @tmpSql + ' ISNULL(SUM(' + PayType  + '), 0) AS ''' + PayType  + ''','  + CHAR(10)
                     FROM  (SELECT DISTINCT PayType FROM #Inpours ) T
 print @tmpSql
 SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ' FROM (' + CHAR(10);
 print @tmpSql
 SET @cmdText = @tmpSql + @cmdText + ') T GROUP BY CreateTime ';
 PRINT @cmdText
 EXECUTE (@cmdText);
 -------
 SELECT
         CreateTime, [支付宝] , [手机短信],[工商银行卡] , [建设银行卡]
 FROM
 (
     SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money
     FROM #Inpours
 ) P
 PIVOT (
             SUM(Money)
             FOR PayType IN
             ([支付宝], [手机短信], [工商银行卡], [建设银行卡])
       ) AS T
 ORDER BY CreateTime
 
 ----
 /*
 消息 325，级别 15，状态 1，第 9 行
 
 'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。
 这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。
 有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。
 例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。
 **/
 
 
 ------------------------------------
 CREATE TABLE #ProgrectDetail
 (
     ProgrectName         NVARCHAR(20), --工程名称
     OverseaSupply        INT,          --海外供应商供给数量
     NativeSupply         INT,          --国内供应商供给数量
     SouthSupply          INT,          --南方供应商供给数量
     NorthSupply          INT           --北方供应商供给数量
 )
 --drop table #ProgrectDetail
 
 INSERT INTO #ProgrectDetail
 SELECT 'A', 100, 200, 50, 50
 UNION ALL
 SELECT 'B', 200, 300, 150, 150
 UNION ALL
 SELECT 'C', 159, 400, 20, 320
 UNION ALL
 SELECT 'D', 250, 30, 15, 15
 
 select * from #ProgrectDetail
 ------
 SELECT ProgrectName, 'OverseaSupply' AS Supplier,
         MAX(OverseaSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 UNION ALL
 SELECT ProgrectName, 'NativeSupply' AS Supplier,
         MAX(NativeSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 UNION ALL
 SELECT ProgrectName, 'SouthSupply' AS Supplier,
         MAX(SouthSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 UNION ALL
 SELECT ProgrectName, 'NorthSupply' AS Supplier,
         MAX(NorthSupply) AS 'SupplyNum'
 FROM #ProgrectDetail
 GROUP BY ProgrectName
 --------
 SELECT ProgrectName,Supplier,SupplyNum
 FROM
 (
     SELECT ProgrectName, OverseaSupply, NativeSupply,
            SouthSupply, NorthSupply
      FROM #ProgrectDetail
 )T
 UNPIVOT
 (
     SupplyNum FOR Supplier IN
     (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
 ) P

 

View Code
 ----------------------行转列问题-----------------
 CREATE TABLE #test(
   EmployeeID  INT,
   CardTime    DATETIME
 );
 go
 
 INSERT INTO #test
   SELECT 1, '2011-08-01 08:01:27' UNION ALL
   SELECT 1, '2011-08-01 11:33:27' UNION ALL
   SELECT 1, '2011-08-01 13:32:27' UNION ALL
   SELECT 1, '2011-08-01 17:30:27' UNION ALL
   SELECT 2, '2011-08-01 08:22:27' UNION ALL
   SELECT 2, '2011-08-01 11:21:27' UNION ALL
   SELECT 2, '2011-08-01 17:32:27'
 go
 
 
 --------------------------------
 with myCTE AS (
 SELECT
   c.EmployeeID,
   c.CardTime,
   CASE
     WHEN (convert(varchar(10), c.CardTime ,8) between '07:00:00' and '10:00:00')
       THEN '上午上班时间' 
     WHEN ( convert(varchar(10), c.CardTime ,8) between '11:00:00' and '13:00:00')
       THEN '上午下班时间'
     WHEN ( convert(varchar(10), c.CardTime ,8) between '13:00:00' and '15:00:00')
       THEN '下午上班时间 '
     ELSE
       '下午下班时间'
   END as TimePart
 FROM
   #test c
 )
 SELECT
   *
 FROM
   myCTE
 PIVOT(
   MIN(CardTime)
   FOR TimePart IN ([上午上班时间], [上午下班时间], [下午上班时间], [下午下班时间] )
 ) tmp
 
 -----------------------------
 with myCTE AS (
 SELECT
   c.EmployeeID,
   CONVERT(varchar(10), c.CardTime, 120) AS CardDate,
   c.CardTime,
   CASE
     WHEN (convert(varchar(10), c.CardTime ,8) between '07:00:00' and '10:00:00')
       THEN '上午上班时间' 
     WHEN ( convert(varchar(10), c.CardTime ,8) between '11:00:00' and '13:00:00')
       THEN '上午下班时间'
     WHEN ( convert(varchar(10), c.CardTime ,8) between '13:00:00' and '15:00:00')
       THEN '下午上班时间 '
     ELSE
       '下午下班时间'
   END as TimePart
 FROM
   #test c
 ),
 baseCTE AS (
 SELECT
   EmployeeID,
   CardDate,
   MAX( CASE WHEN TimePart='上午上班时间' THEN  CardTime ELSE NULL END ) AS [上午上班时间],
   MIN( CASE WHEN TimePart='上午下班时间' THEN  CardTime ELSE NULL END ) AS [上午下班时间],
   MAX( CASE WHEN TimePart='下午上班时间' THEN  CardTime ELSE NULL END ) AS [下午上班时间],
   MIN( CASE WHEN TimePart='下午下班时间' THEN  CardTime ELSE NULL END ) AS [下午下班时间]
 FROM
   myCTE
 GROUP BY
   EmployeeID,
   CardDate
 )
 SELECT
   EmployeeID,
   CardDate,
   [上午上班时间],
   [上午下班时间],
   [下午上班时间],
   [下午下班时间],
   CASE
     WHEN (convert(varchar(10), [上午上班时间] ,8) between '08:35:00' and '10:00:00')
       THEN 1
     ELSE 0
   END AS [上午迟到],
   CASE
     WHEN (convert(varchar(10), [上午下班时间] ,8) between '10:00:00' and '12:00:00')
       THEN 1
     ELSE 0
   END AS
   [上午早退],
   CASE
     WHEN (convert(varchar(10), [下午上班时间] ,8) between '14:05:00' and '15:00:00')
       THEN 1
     ELSE 0
   END AS
   [下午迟到],
   CASE
     WHEN (convert(varchar(10), [下午下班时间] ,8) between '17:00:00' and '17:30:00')
       THEN 1
     ELSE 0
   END AS [下午早退]
 FROM
   baseCTE;

 

 

将查询出来的结果集转换为XML类型

View Code
 /**
 Select 的查询结果会作为行集返回，但是你同样可以在sql中指定for xml子句使得查询作为xml来检索。
 在for xml子句中，可以指定以下模式之一：RAW 、AUTO、EXPLICIT和PATH。
 RAW模式返回行为元素，每一列的值作为元素的属性；
 AUTO模式返回表名为节点的元素，每一列的属性作为属性输出；
 EXPLICIT模式通过SELECT语法定义输出XML结构；
 PATH模式中列名或列别名作为XPATH表达式来处理。
 **/
 --xml raw
 SELECT TOP 5 CCode,CName FROM dbo.city FOR XML RAW
 --xml auto
 SELECT TOP 5 ProductName,UnitPrice FROM dbo.Products FOR XML AUTO
 --xml explict
 SELECT TOP 5 1 AS Tag,0 AS Parent, PCode AS [Province!1!ID],PName AS [Province!1!Name],PCode AS [Province!1!Customer],NULL AS [City!2!ID],NULL AS [City!2!CName],NULL AS [City!2!CityID]
     FROM dbo.Province WHERE dbo.Province.PCOde='105'
 UNION ALL
 SELECT TOP 5 2 AS Tag,1 AS Parent, NULL,NULL ,NULL,ID,CName,0 FROM dbo.city WHERE PCOde='105'
 FOR XML EXPLICIT
 --PATH
 SELECT TOP 5 CName AS CityName, CCode AS CityID FROM dbo.City FOR XML PATH('City')
 /**
 RAW模式和AUTO模式相对比较简单，灵活性当然也是比较差的。
 EXPLICIT模式会将查询执行生成的行集转换为XML文档，也就是说结构可以自定义，当然这必须符合一定的格式。
 就拿上面的语句来说：Tag指定生成节点的嵌套级别，列名必须为"Tag"例如上面的级别为1。
 Parent指定当前Tag的父级层次，列名必须为"Parent"，NULL表示顶级。
 其他列名，例如[Order!1!ID]分别代表元素名称、Tag标记（也就是层级）、属性名称。
 例如上面的这一列就代表会在第一级节点中生成一个节点名称为Order的节点并且有一个属性ID。
 此外还需要指出的是这个列名还有一个可选部分，完整的形式是[ElementName!TagNumber!AttributeName!Directive]
 ，最后一部分Directive是可选的，如果不指定的话默认作为ElementName中的属性名称；
 当然如果制定了xml、cdata或者element那么它将作为ElementName的一个子元素而不是属性。
 并且指定了此选项之后AttributeName可以为空。
 上面的例子中如果去掉For XML EXPLICIT之后查询到的数据时如下形式：
 
 按照上面的规则不难看出，FOR XML EXPLICIT会按照顺序生成xml：
 第一次执行（第一行数据）时tag为1，parent为0，
     则此时会按照列名创建一个Order节点，并依次创建三个属性：ID、Date和Customer，
     其值为：10248、1996-07-04 00:00:00.000、VINET。此时不会生成ProductID属性，
     因为它的Tag为2，需要在第二级节点中创建，其他另个属性也是如此。
 第二次执行（第二行数据）时Tag为2，Parent为1，
     也就是要在Tag为1的节点中创建一个2级节点。
     然后根据查找列名中Tag为2的列，依次创建ProductID、UnitPrice、Quantity属性，并赋值。
 第三次、第四次同第二次执行类似，不同的只是数据而已。
 PATH模式应该说是使用率相当高的，原因比较简单，它的使用既简单又灵活。
 在PATH模式中，节点名称可以使列名也可以通过别名指定又或者根本不指定，不指定的情况下就只显示节点内数据；
 根节点可以指定也可以不指定，不指定情况下默认为"row"，并且根节点指定为""的情况下可以完全取消根节点的显示。
 有了这一属性就可以使用PATH模式完成很多有用的操作。
 没有指定根节点时：
 
 */
 --没有指定根节点时
 SELECT TOP 5 CName FROM dbo.City FOR XML PATH
 --去掉根节点名称
 SELECT TOP 5 CName FROM dbo.City FOR XML PATH('')
 --去掉根节点和子节点名称
 SELECT TOP 5 CName+',' FROM dbo.City FOR XML PATH('')
 --可以发现去掉节点名称之后for xml path起到了字符串连接的作用，这样一来就可以利用这一特性完成字符串拼接
 SELECT LEFT(CName,(LEN(CName)-1)) FROM(
 SELECT
 (SELECT TOP 5 CName+',' FROM dbo.City FOR XML PATH('')) AS CName
 ) AS T
 
 
 select * from City