TransWikia.com

Unpivot a single into two rows - T-SQL

Stack Overflow Asked by Error 1004 on December 2, 2021

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;

One Answer

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)

Answered by GMB on December 2, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP