Query Help to show record in single row

• sqldba20

  Ten Centuries

  Points: 1310

  More actions

  February 21, 2025 at 5:30 pm

  Go to Answer

  #4543155

  Hi All, need help with an SQL query.

  We have an existing table that's already formatted this way, and I am having trouble extracting the data in single record.

  Current Output:

  

  EXPECTED OUTPUT: The EndDate and EndTime I want it displayed in the same row where BName like '% Start'

  

  I tried to do this through cursor by updating the EndDate and EndTime, but that is not working. Below is an test query I was trying it against.

     DECLARE @tblData TABLE
     (BName varchar(50),
      StartDate varchar(10),
      StartTime varchar(10),
      EndDate varchar(10),
      EndTime varchar(10))
  
     INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('STAT Start','02/20/2025','00:34:02')
     INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('STAT End','02/20/2025','00:40:36')
     INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('CDS Start','02/20/2025','00:40:38')
     INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('CDS End','02/20/2025','00:47:26')
     INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('LED Start','02/20/2025','00:40:42')
     INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('LED End','02/20/2025','00:54:28')

   

  Thanks!

   

   

• ScottPletcher

  SSC Guru

  Points: 101200

  More actions

  February 21, 2025 at 6:02 pm

  Answer

  #4543189

  SELECT
      LEFT(Bname, CHARINDEX(' ', BName) - 1) AS BName,
      MAX(CASE WHEN BName LIKE '% Start%' THEN StartDate ELSE '' END) AS StartDate,
      MAX(CASE WHEN BName LIKE '% Start%' THEN StartTime ELSE '' END) AS StartTime,
      MAX(CASE WHEN BName LIKE '% End%' THEN StartDate ELSE '' END) AS EndDate,
      MAX(CASE WHEN BName LIKE '% End%' THEN StartTime ELSE '' END) AS EndTime
  FROM @tblData 
  GROUP BY LEFT(Bname, CHARINDEX(' ', Bname) - 1)
  ORDER BY 1, 2

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Steve Collins

  SSCrazy

  Points: 2227

  More actions

  February 22, 2025 at 9:44 pm

  #4543919

  with stub_cte as (
      select BName, StartDate, StartTime, 
             lag(StartDate) over (partition by v.stub order by v.dt desc) EndDate,
             lag(StartTime) over (partition by v.stub order by v.dt desc) EndTime
      from @tblData
           cross apply (values (cast(StartDate as datetime)+cast(StartTime as datetime), 
                                left(Bname, charindex(' ', Bname) - 1))) v(dt, stub))
  select *
  from stub_cte
  where BName like '%Start'
        and EndDate is not null;

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können