Sql-server – Help with a pivot query

pivotsql server

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!

Best Answer

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  |

Related Solutions

Sql-server – Help with a PIVOT

You are on the right track with your current query but I would alter it slightly to use a subquery and include a row_number() to give a distinct value to each score in your rank.

If you query your data using:

select [rank], score,
  row_number() over(partition by [rank] order by [rank]) rn
from yourtable

See Demo

You will get a sequenced number for each score inside your ranks. This sequence number will then be grouped by in your PIVOT making your full code:

select [1], [2], [3]
from
(
  select [rank], score,
    row_number() over(partition by [rank] order by [rank]) rn
  from yourtable
) d
pivot
(
  max(score)
  for [rank] in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. This query gives you a result:

| 1 |   2 | 3 |
|---|-----|---|
| 1 | 2.5 | 5 |
| 7 | 2.5 | 8 |
| 9 | 2.5 | 5 |

Mysql – Pivot table with MySQL

Give this a try,tested on my machine

First make sure GROUP_CONCAT has plenty of space:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(DISTINCT CASE WHEN buyer_country= ''',
      buyer_country,
      ''' THEN transactions.buyer_id END) AS ',
      buyer_country
    )
   )INTO @sql
FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id;


SET @sql=CONCAT('SELECT transactions.seller_country,',@sql,' FROM  transactions 
LEFT JOIN buyers on transactions.buyer_id=buyers.buyer_id GROUP BY transactions.seller_country');



PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link

Edit

ALTER TABLE transactions ADD key trans_idx (buyer_id)
ALTER TABLE buyers ADD key buyers_idx (buyer_id)

Best Answer

Related Solutions

Sql-server – Help with a PIVOT

Mysql – Pivot table with MySQL

Related Question