Query Help to show record in single row

Question

Hi All, need help with an SQL query.Currently we are getting Start and End BNames on separate rows.   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 Start','02/20/2025','00:54:28')Current Output:EXPECTED OUTPUT: The EndDate and EndTime I want it displayed in the same row where BName like '% Start'&nbsp;&nbsp;I tried to do this through cursor by updating the EndDate and EndTime, but that is not working.Any help will be appreciated.&nbsp;Thanks!

yuriyzaletskyy · Answer

You can achieve this by using LEFT JOIN and matching the "Start" and "End" entries based on the BName prefix. Here's an SQL query to format your expected output:&nbsp;DECLARE @tblData TABLE
   (BName varchar(50),
    StartDate varchar(10),
    StartTime 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')

SELECT 
    StartData.BName, 
    StartData.StartDate, 
    StartData.StartTime, 
    EndData.StartDate AS EndDate, 
    EndData.StartTime AS EndTime
FROM 
    @tblData StartData
LEFT JOIN 
    @tblData EndData 
    ON REPLACE(StartData.BName, ' Start', '') = REPLACE(EndData.BName, ' End', '')
    AND StartData.StartDate = EndData.StartDate
    AND EndData.BName LIKE '% End'
WHERE 
    StartData.BName LIKE '% Start'
ORDER BY 
    StartData.StartDate, StartData.StartTime;&nbsp;

sivertsolem · Answer

Your insert statements confound me.Do you have a table that's already formatted that way, and you're having trouble extracting the data the way you want it, or are you struggling with inserting and updating rows to your table?Changing your "End" inserts to updates will at least leave the table in the format you requested. 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','02/20/2025','00:34:02')
   UPDATE @tblData set EndDate = '02/20/2025',  EndTime= '00:40:36' where BName = 'STAT'
   INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('CDS','02/20/2025','00:40:38')
   UPDATE @tblData set EndDate = '02/20/2025', EndTime = '00:47:26' where BName = 'CDS'
   INSERT INTO @tblData (BName, StartDate, StartTime) VALUES ('LED','02/20/2025','00:40:42')
   UPDATE @tblData set EndDate = '02/20/2025', EndTime = '00:54:28' where BName = 'LED'

Select * from @tblData
   &nbsp;

sqldba201400 · Answer

Sorry I was not clear in my ask. I have a table that's already formatted that way, and I am having trouble extracting the data the way I want it.&nbsp;

sqldba201400 · Answer

Sorry I was not clear in my ask. I have the table that's already formatted that way, and I am having trouble extracting the data the way I want it.&nbsp;

sivertsolem · Answer

This should work then.Note that in your sample table, you've inserted 'LED Start' twice.I changed the second to 'LED End', as that is in line with your expected output.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')

/* Do a self join on the unique part of the Bname */
SELECT st.BName, st.StartDate, st.StartTime, en.StartDate as EndDate, en.StartTime as EndTime
FROM @tblData st JOIN @tblData en 
/* Compares the substring before ' Start' with the substring before ' End', joining STAT Start with STAT End, CDS Start with CDS End etc. */
on SUBSTRING (st.BName, 0, CHARINDEX(' Start', st.Bname, 0)) = SUBSTRING (en.BName, 0, CHARINDEX(' End', en.Bname, 0))
where st.BName like '%start'It's also possible to use a substring statement in the select row to convert the column to just STAT, CDS, LED./* Do a self join on the unique part of the Bname */
SELECT SUBSTRING (st.BName, 0, CHARINDEX(' Start', st.Bname, 0)) as Name, st.StartDate, st.StartTime, en.StartDate as EndDate, en.StartTime  as EndTime
FROM @tblData st JOIN @tblData en 
/* Compares the substring before ' Start' with the substring before ' End', joining STAT Start with STAT End, CDS Start with CDS End etc. */
on SUBSTRING (st.BName, 0, CHARINDEX(' Start', st.Bname, 0)) = SUBSTRING (en.BName, 0, CHARINDEX(' End', en.Bname, 0))
where st.BName like '%start'I will not claim that this is the most efficient way to solve your select, but it works assuming the BName column is unique, i.e. there's a single start and end pr. prefix.If there's duplicates, this will return the cross product of the dupes, matching each possible start row with each possible end row, as shown in this result where CDS has been renamed STAT as well.&nbsp;

Forum Discussion

Query Help to show record in single row

6 Replies

Resources