Transpose Rows to columns without Aggregate

• greeshatu

  Right there with Babe

  Points: 791

  More actions

  May 19, 2017 at 3:54 am

  #342928

  Hi 

  CREATE TABLE [dbo].[Table1](
      [RuleID] [nvarchar](10) NULL,
      [DateTime1] [datetime] NULL,
      [DName] [nvarchar](30) NULL
  ) ON [PRIMARY]

  GO
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRS', CAST(N'2017-03-28T12:22:04.000' AS DateTime), N'DB1')
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRK', CAST(N'2017-03-28T12:22:04.260' AS DateTime), N'DB1')
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRE', CAST(N'2017-03-28T12:22:09.000' AS DateTime), N'DB1')
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRK', CAST(N'2017-04-04T08:33:15.870' AS DateTime), N'DB2')
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRE', CAST(N'2017-04-04T08:33:31.000' AS DateTime), N'DB2')
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRK', CAST(N'2017-04-04T09:14:30.503' AS DateTime), N'DB2')
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRS', CAST(N'2017-04-04T09:14:31.000' AS DateTime), N'DB2')
  INSERT [dbo].Table1 ([RuleID], [DateTime1], [DName]) VALUES (N'DBRE', CAST(N'2017-04-04T09:44:33.000' AS DateTime), N'DB2')

  SELECT * FROM TABLE1
  DESIRED OUPUT:
  SELECT 'DB1' As DName,'2017-03-28 12:22:04.260'AS BRKIndicated ,'2017-03-28 12:22:04.000' As BRKStart,'2017-03-28 12:22:09.000' AS BRKEnd
  UNION
  SELECT 'DB2'As DName,'2017-04-04 08:33:15.870'AS BRKIndicated,NULL As BRKStart,'2017-04-04 08:33:31.000'AS BRKEnd
  UNION
  SELECT 'DB2'As DName,'2017-04-04 09:14:30.503'AS BRKIndicated,'2017-04-04 09:14:31.000' As BRKStart,'2017-04-04 09:44:33.000'AS BRKEnd

  Objective: Transpose rows to columns against RULEID and each DName

• Mark Cowne

  One Orange Chip

  Points: 26865

  More actions

  May 19, 2017 at 4:37 am

  #1943247

  Try this

  
WITH Src AS (
SELECT RuleID,
   DateTime1,
     DName,
     CASE WHEN LAG(RuleID) OVER(PARTITION BY DName ORDER BY DateTime1) <> 'DBRE' THEN 0 ELSE 1 END AS isstart
FROM dbo.Table1),
Grouped AS (
SELECT RuleID,
   DateTime1,
     DName,
     SUM(isstart) OVER(PARTITION BY DName ORDER BY DateTime1) AS grp
FROM Src)
SELECT DName,
   MAX(CASE WHEN RuleID = 'DBRK' THEN DateTime1 END) AS BRKIndicated,
   MAX(CASE WHEN RuleID = 'DBRS' THEN DateTime1 END) AS BRKStart,
   MAX(CASE WHEN RuleID = 'DBRE' THEN DateTime1 END) AS BRKEnd
FROM Grouped
GROUP BY DName,grp
ORDER BY DName,BRKIndicated;


  Edit : missed PARTITION BY

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537