Transform table by pivoting/transposing

1

Hi.

I have a fairly large table that looks like this:

customer_id opening_location reallocated_location
001 100 101
002 100 100
003 100 101
004 102 101
005 102 102
006 103 104
007 105 101

And I would like to transform it into two tables, one specific to the opening and the other to the reallocation. The resultant table will look something like this where the locations are now the columns, and for each location, the (opening) customers will be listed as rows.

100 101 102
001 .. ..
002 .. ..
003 .. ..

I am not sure how to approach this. I thought about pivoting (pivot function) but that requires aggregation, which I don't believe I need. I'm using Teradata but if there's a more universal way of achieving this, please direct me.

Edit: Added workable example.

CREATE TABLE IF NOT EXISTS `tsa` (
  `customer_id` varchar(3) NOT NULL,
  `opening_location` int(3) unsigned NOT NULL,
  `reallocation_location` int(3) unsigned NOT NULL,
  PRIMARY KEY (`customer_id`)
);

INSERT INTO `tsa` (`customer_id`, `opening_location`, `reallocation_location`) VALUES
  ('001', '100', '100'),
  ('002', '101', '100'),
  ('003', '101', '101'),
  ('004', '102', '100'),
  ('005', '103', '101'),
  ('006', '104', '104'),
  ('007', '105', '102');
2

Please provide directly usable data -- that is, CREATE TABLE and INSERT statement(s) -- rather than just a picture of data. We can't write SQL against a picture :grinning:

3

Added it, thanks. I used MySQL.

4

Oops, I don't know MySQL. This is a SQL Server forum, but hopefully someone who knows MySQL will see this and offer an answer.

5

run with this maybe?

  SELECT MAX(CASE WHEN reallocation_location = "100" THEN customer_id END) "100",
         MAX(CASE WHEN reallocation_location = "101" THEN customer_id END) "101"
FROM tsa
GROUP BY  reallocation_location, customer_id
6

Yes this works, but I have over 500 locations (and a few more to be added). I can do it but I hoped there was a more 'automatic' way I guess. Cheers.

7

you might have to do it with dynamic sql, not sure if this is possible in mysql. search for dynamic sql in mysql forums.