如何在SQL Server中旋转表 [英] How to rotate a table in SQL Server

问题描述

我有三列ticketid表，所有者，owendate as-

 ticketid所有者owndate 
 
 1001 LEWIS 2004-06-18 14:15:11 
 1001 WILSON 2004-06-18 14:16:54 
 1001 WILSON 2004-06-18 14:21:12 
 1001 （null）2004-09-01 09:56:11 
 1001 CALDONE 2005-02-02 08:38:28 
 1001 SINCLAIR 2005-02-02 08:54:02 
 1002 （null）2005-02-02 08:40:06 
 1002 REID 2015-01-16 15:18:05 
 1002 SMALL 2015-01-16 15:19:53 
 1002 WILSON 2004-06-18 14:33:47 
 1002（null）2004-08-31 15:12:46 
 1002（null）2004-09-24 10:03:09 
 1003 RAMSDALE 2004-09-24 10:04:24 
 1003 MOTIKA 2004-08-31 14:51:45 
 1003（null）2004-08-31 15:05:50 
 1003 MURTHY 2004-09-02 14:50:28 
 1004（null）2004-08-31 15:28:37 
 1004（null） 2004-09-24 09:24:21 
 1005 MOTIKA 2004-08-31 14:47:19 
 1005 MOTIKA 2004-08-31 17:20:33 
 1005（null） 2004-08-31 17:23:42 
 1005 WILSON 2004-09-02 16:29:43 
 1005 DALEY 2004-09-15 08:45:27 
 1005 SINCLAIR 2004- 09-15 08:34:34 

我希望看到每张票的票价时差为......

 ticketid owner1 owner2 ... 
 1001 datediff（owndate1-owndate2）datediff（owndate2-owndate3）... 

解决方案

您可以使用LAG或LEAD函数从结果集中的上一行或下一行中获取值。



查看如何从结果集中的上一行或下一行中获取数据 [ ^ ]



加法



也许类似

  SELECT  a.TicketId，
 a.Owner，
 a.OwnDate  -  b.OwnDate 
 从 TicketTable a 
  LEFT   OUTER   JOIN  TicketTable b 
  ON  a.TicketId = b.TicketId 
  AND  a.Owner = b.Owner 
  AND  a.OwnDate< b.OwnDate 
  WHERE   NOT   EXISTS （ SELECT   1  
  FROM  TicketTable c 
  WHERE  c.TicketId = a.TicketId 
  AND  c .Owner = a.Owner 
  AND  c.OwnDate< a.OwnDate）

< blockquote>最后用我的要求解决了这个问题......

 选择 ticketid，
 owner1 = CONVERT（ varchar （ 10 ），owner2-owner1 ， 108 ），
 owner2 = CONVERT（ varchar （ 10 ），owner3-owner2， 108 ），
 owner3 = CONVERT（ varchar （ 10 ），owner4-owner3， 108 ），
 owner4 = CONVERT（ varchar （< span class =code-digit> 10 ），owner4， 108 ）
 来自 
（
 选择 ticketid，owndate，'  owner' + cast（ROW_NUMBER（） over  
（ partition   by  ticketid  order   by  owndate ） as   varchar （ 10 ））< span class =code-keyword> as  sno  from  tkownerhistory 
）tbl 
 pivot 
（
 max（owndat e）
   sno  （owner1，owner2，owner3，owner4）
 
）pvt 

I have table with three columns ticketid,owner,owendate as-

ticketid   owner                     owndate

1001	   LEWIS	        2004-06-18 14:15:11
1001	   WILSON	        2004-06-18 14:16:54
1001	   WILSON	        2004-06-18 14:21:12
1001	   (null)	        2004-09-01 09:56:11
1001	   CALDONE	        2005-02-02 08:38:28
1001	   SINCLAIR	        2005-02-02 08:54:02
1002	   (null)	        2005-02-02 08:40:06
1002	   REID	                2015-01-16 15:18:05
1002	   SMALL	        2015-01-16 15:19:53
1002	   WILSON	        2004-06-18 14:33:47
1002	   (null)	        2004-08-31 15:12:46
1002	   (null)	        2004-09-24 10:03:09
1003	   RAMSDALE	        2004-09-24 10:04:24
1003	   MOTIKA	        2004-08-31 14:51:45
1003	   (null)	        2004-08-31 15:05:50
1003	   MURTHY	        2004-09-02 14:50:28
1004	   (null)	        2004-08-31 15:28:37
1004	   (null)	        2004-09-24 09:24:21
1005	   MOTIKA	        2004-08-31 14:47:19
1005	   MOTIKA	        2004-08-31 17:20:33
1005	   (null)	        2004-08-31 17:23:42
1005	   WILSON	        2004-09-02 16:29:43
1005	   DALEY	        2004-09-15 08:45:27
1005	   SINCLAIR	        2004-09-15 08:34:34

And I want to see output as ticket time difference for each ticket as...

ticketid                  owner1                          owner2               ...   
1001            datediff(owndate1-owndate2)     datediff(owndate2-owndate3)    ...

解决方案

You can use either LAG or LEAD functions to fetch values from previous or next rows in the result set.

Have a look at How to fetch data from the previous or next rows in the resultset[^]

Addition

Perhaps something like

SELECT a.TicketId,
       a.Owner,
       a.OwnDate - b.OwnDate
FROM   TicketTable a
       LEFT OUTER JOIN TicketTable b
       ON a.TicketId = b.TicketId 
       AND a.Owner = b.Owner
       AND a.OwnDate < b.OwnDate
WHERE NOT EXISTS (SELECT 1
                  FROM  TicketTable c
                  WHERE c.TicketId = a.TicketId 
                  AND   c.Owner = a.Owner
                  AND   c.OwnDate < a.OwnDate)

Finally solved this problem with my requirements......

select ticketid,
owner1=CONVERT(varchar(10),owner2-owner1,108),
owner2=CONVERT(varchar(10),owner3-owner2,108),
owner3=CONVERT(varchar(10),owner4-owner3,108),
owner4=CONVERT(varchar(10),owner4,108)
from
(
select ticketid,owndate,'owner'+cast(ROW_NUMBER() over
(partition by ticketid order by owndate) as varchar(10)) as sno from tkownerhistory
) tbl
pivot
(
max(owndate)
for sno in(owner1,owner2,owner3,owner4)

)pvt

这篇关于如何在SQL Server中旋转表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！