sqlsql

sql.sql

create table #CHANNEL(
	pk [int] IDENTITY(1,1) NOT NULL,
	ChannelNumber nvarchar(10) NULL,
	DisplayName nvarchar(10) NULL,
	ChannelID int NOT NULL,
	PRIMARY KEY CLUSTERED  (pk ASC)
)

CREATE NONCLUSTERED INDEX 
tmp_indx_channel ON #CHANNEL (ChannelID ASC)

CREATE NONCLUSTERED INDEX 
tmp_indx_full ON #CHANNEL (ChannelID ASC)
INCLUDE (ChannelNumber, DisplayName)

insert into #channel values ('sth', 'a', 1)
insert into #channel values ('sth', 'a', 2)
insert into #channel values ('sth', 'ahd', 2)

SELECT CONVERT(CHAR(10), N1.ChannelNumber) AS 'Standard Channel',
    CONVERT(CHAR(10), N1.DisplayName) AS 'Standard Name',
    CONVERT(CHAR(10), N2.ChannelNumber) AS 'HD Channel',
    CONVERT(CHAR(10), N2.DisplayName) AS 'HD Name'
FROM #CHANNEL AS N1 
JOIN #CHANNEL AS N2 ON N1.ChannelID = N2.ChannelID AND N1.DisplayName = N2.DisplayName + 'HD'


SELECT CONVERT(CHAR(10), N1.ChannelNumber) AS 'Standard Channel',
    CONVERT(CHAR(10), N1.DisplayName) AS 'Standard Name',
    CONVERT(CHAR(10), N2.ChannelNumber) AS 'HD Channel',
    CONVERT(CHAR(10), N2.DisplayName) AS 'HD Name'
FROM #CHANNEL AS N1 
JOIN #CHANNEL AS N2 ON N1.ChannelID = N2.ChannelID AND N1.DisplayName LIKE N2.DisplayName + 'HD'