CODEWITHSUNDEEP
sql-servert-sqljoinunpivot
Error 1004
Error 1004

Reputation: 8230

Unpivot a single into two rows - T-SQL

I'm trying to unpivot the below row but I'm facing troubles. Any help please?

Input:

Date        Time    Country_Championship    Game      Home_Team   Away_Team
----------------------------------------------------------------------------
19/07/2020  18:45   Albania: Superliga  Bylis Vs Laci   Bylis       Laci

Output:

Date        Time    Country_Championship    Game        Team
---------------------------------------------------------------
19/07/2020  18:45   Albania: Superliga  Bylis Vs Laci   Bylis
19/07/2020  18:45   Albania: Superliga  Bylis Vs Laci   Laci

What I have tried:

SELECT 
    u.[Date], u.[Time], 
    u.[Country_Championship],
    u.[Game],
    u.[Home_Team], u.[Away_Team],
    u.details
FROM 
    [Soccer_Analytics].[dbo].[Ins_UpcomingGames_Temp] t
UNPIVOT
    (details FOR [Date], [Time], [Country_Championship], [Game] IN ([Home_Team], [Away_Team])) u;

Upvotes: 0

Views: 36

Answers (1)

GMB
GMB

Reputation: 222582

In T-SQL, I would recommend a lateral join for this:

select x.*
from [Soccer_Analytics].[dbo].[Ins_UpcomingGames_Temp] t 
cross apply (values
    (date, time, country_championship, game, home_team),
    (date, time, country_championship, game, away_team)
) as x(date, time, country_championship, game, team)

Upvotes: 2

Related Questions