Forum Discussion
Query Help to show record in single row
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
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.
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.
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.
