Post reply

Transpose Rows to columns without Aggregate

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply