CODEWITHSUNDEEP
sqlarrays
user3038725
user3038725

Reputation: 321

Work around SQL array

I know this question might have been asked many times, but I failed to find them. My original table is like this

    Location Language Component Score
    A        Eng         1        25
    A        Eng         2        30
    B        Eng         1        12
    B        Eng         2        50

Expected Output:

    Location Language  Score1    Score2
    A        Eng         25       30
    B        Eng         12       50

Thanks!

Upvotes: 0

Views: 36

Answers (2)

jbaliuka
jbaliuka

Reputation: 259

SELECT 
  Location, 
  Language,
  SUM(CASE WHEN Component = 1 THEN Score ELSE 0 END) AS  Score1,
  SUM(CASE WHEN Component = 2 THEN Score ELSE 0 END) AS  Score2,
FROM MY_TABLE
GROUP BY Location, Language

Upvotes: 1

Ash8087
Ash8087

Reputation: 701

Try this...

    SELECT DISTINCT S1.[Location], S1.[Language], S1.Score1, S2.Score2
    FROM
        (SELECT [Location], [Language], Score [Score1]
        FROM Scores WHERE Component = 1) S1
        LEFT JOIN 
        (SELECT [Location], [Language], Score [Score2]
        FROM Scores WHERE Component = 2) S2 ON S1.[Location] = S2.[Location] 
            AND S1.[Language] = S2.[Language]

Upvotes: 0

Related Questions