@SQL With single column i need to get below mentioned output like below

Question

I am having following data in a table

**TEAM NAME**
Germany
Holland 
Scotland
Brazil

I AM Expecting data to be like below with same order, Please help

**ScheduledMatches**
Germany VS Holland
Germany VS Scoltland
Germany VS Brazil
Holland VS Scoltland
Holland VS Brazil
Scoltland VS Brazil

Thanks Vijay Sagar

Answer 1

You can first calculate ROW_NUMBER for each team and use self join with < based on calculated rn:

CREATE TABLE #teams(name VARCHAR(100));

INSERT INTO #teams(name)
VALUES('Germany'), ('Holland'), ('Scotland'), ('Brazil');

;WITH cte AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
  FROM #teams
)
SELECT CONCAT(c1.name, ' vs ',  c2.name) AS result  
FROM cte c1
JOIN cte c2 
  ON c1.rn < c2.rn
-- ORDER BY result;

LiveDemo

CONCAT is available from SQL Server 2012+ if you have lower version use + to concatenate string.

Answer 2

DECLARE @t TABLE (Team VARCHAR(100))
INSERT INTO @t (Team)
VALUES
    ('Germany'),
    ('Holland'), 
    ('Scotland'),
    ('Brazil')

;WITH cte AS 
(
    SELECT *, RowNum = ROW_NUMBER() OVER (ORDER BY 1/0)
    FROM @t   
)
SELECT t2.Team + ' vs ' + t1.Team
FROM cte t1
JOIN cte t2 ON t1.RowNum > t2.RowNum

output -

---------------------------
Germany vs Holland
Germany vs Scotland
Germany vs Brazil
Holland vs Scotland
Holland vs Brazil
Scotland vs Brazil

Answer 3

Below code will help you!!!

SELECT
    a.M +' VS '+ b.M AS [**ScheduledMatches**]
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 1 order by 1/0) AS no
    FROM (
        VALUES ('Germany'), ('Holland'), ('Scotland'), ('Brazil')
    ) t (m)
) a
JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 1 order by 1/0) AS no
    FROM (
        VALUES ('Germany'), ('Holland'), ('Scotland'), ('Brazil')
    ) t (m)
) b ON a.no < b.no

Output:

 **ScheduledMatches**
Germany VS Holland
Germany VS Scotland
Germany VS Brazil
Holland VS Scotland
Holland VS Brazil
Scotland VS Brazil