2

I've got data that goes sort of like this:

Time    Name    country
12      steve   brazil
12      mary    brazil
12      kate    usa
12      john    usa
13      paul    canada
13      tim     canada

How would i go about writing a pivot query so that it looks like this?:

Time    Name1    Name2   Country
12      steve    mary    brazil
12      kate     john    usa
13      paul     tim     canada

Thanks!

  • 2
    Are there always only 2 names for each Time and Country? Also, does it matter which name is Name1 and which name is Name2? – Scott Hodgin May 16 at 13:38
2

Here is a quick example of using min and max along with group by to produce a result that is similar to yours.

--Demo setup
drop table if exists table1
go
CREATE TABLE Table1 (
  Time INTEGER,
  Name VARCHAR(5),
  country VARCHAR(6)
);

INSERT INTO Table1
  (Time, Name, country)
VALUES
  ('12', 'steve', 'brazil'),
  ('12', 'mary', 'brazil'),
  ('12', 'kate', 'usa'),
  ('12', 'john', 'usa'),
  ('13', 'paul', 'canada'),
  ('13', 'tim', 'canada');

--Solution
SELECT [Time]
    ,min(NAME) AS Name1
    ,max(NAME) AS Name2
    ,Country
FROM table1
GROUP BY TIME
    ,country
ORDER BY [Time]

| Time | Name1 | Name2 | Country |
|------|-------|-------|---------|
| 12   | mary  | steve | brazil  |
| 12   | john  | kate  | usa     |
| 13   | paul  | tim   | canada  |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.