mysql查询：行转列，列转行，请不要再羞辱我了

被一道行转列的sql面试题羞辱了，好伤心…
大概有这么一个场景，有三个产品，分别是1，2，3，和三个仓库，分别是01，02，03，三个仓库分别储存三个产品，数量分布如下图：

产品（PID）	仓库(SID)	数量(PNUM)
1	01	10
1	02	8
2	02	11
2	03	5
3	03	5

想得到的结果如下图：

产品（PID）	仓库一 (S1ID)	仓库二(S2ID)	仓库三(S3ID)
1	10	8	0
2	0	11	5
3	0	0	5

请写出sql?
这是一个典型的行转列的问题，
创建表

CREATE TABLE `product_store_count` (
  `PID` varchar(11) DEFAULT NULL,
  `SID` varchar(11) DEFAULT NULL,
  `PNUM` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入测试数据


INSERT INTO product_store_count (PID,SID,PNUM) VALUES('1','01',10);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('1','02',20);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('1','03',10);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('2','01',25);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('2','02',16);
INSERT INTO product_store_count (PID,SID,PNUM) VALUES('3','03',12);

我是这样写的，：

SELECT M.PID,
CASE WHEN M.sum is null then 0 ELSE M.sum END S1ID,
CASE WHEN N.sum is null then 0 ELSE N.sum END S2ID,
CASE WHEN P.sum is null then 0 ELSE P.sum END S3ID
FROM (SELECT PID,SID, SUM(PNUM) sum FROM product_store_count WHERE SID='01' GROUP BY PID,SID) M
LEFT JOIN (SELECT PID,SID, SUM(PNUM) sum FROM product_store_count WHERE SID='02' GROUP BY PID,SID) N
ON M.PID=N.PID
LEFT JOIN (SELECT PID,SID, SUM(PNUM) sum FROM product_store_count WHERE SID='03' GROUP BY PID,SID) P
ON M.PID=P.PID

其实仔细想想我这样写其实是有问题的，以目前的测试数据，查询结果如下，只有仓库01、02的数据，并没有统计出03仓库，事实上03仓库是存有3号产品的。
为什么会有问题呢？仔细看一下测试数据可以发现，01仓库有1、2两种产品，02仓库有1、2两种产品，03仓库只有3，我写的sql还是左连接，向左匹配的结果，就会只有1、2两种产品在01、02、03仓库的库存分布情况，不会有3号产品的。

执行一下这条sqlINSERT INTO product_store_count (PID,SID,PNUM) VALUES(‘3’,‘01’,16);，让01仓库有1、2、3三种产品，再执行sql就会是正确的结果。

其实这么写最大问题就是有时候有可能执行是对的，有时候执行可能结果就是不对，漏掉有数据。生产中千万不能这么写，最好的写法应该是下面的这种写法：

行转列：

SELECT PID,
CASE SID WHEN '01' THEN PNUM ELSE 0 END S1ID,
CASE SID WHEN '02' THEN PNUM ELSE 0 END S2ID,
CASE SID WHEN '03' THEN PNUM ELSE 0 END S3ID
FROM product_store_count;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wSgZFNXR-1577069913815)(https://s2.ax1x.com/2019/12/22/QzpKWd.png)]

SELECT PID,
MAX(CASE SID WHEN '01' THEN PNUM ELSE 0 END ) S1ID ,
MAX(CASE SID WHEN '02' THEN PNUM ELSE 0 END ) S2ID,
MAX(CASE SID WHEN '03' THEN PNUM ELSE 0 END ) S3ID
FROM product_store_count GROUP BY PID;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DYAROmD2-1577069913815)(https://s2.ax1x.com/2019/12/22/Qzp3OP.png)]

那么列转行怎么写呢，看下面：

CREATE TABLE `product_store_count_2` (
  `PID` varchar(11) DEFAULT NULL,
  `S1ID` varchar(11) DEFAULT NULL,
  `S2ID` varchar(11) DEFAULT NULL,
   `S3ID` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `product_store_count_2` (`PID`, `S1ID`, `S2ID`, `S3ID`) VALUES ('1', '10', '20', '10');
INSERT INTO `product_store_count_2` (`PID`, `S1ID`, `S2ID`, `S3ID`) VALUES ('2', '25', '16', '0');
INSERT INTO `product_store_count_2` (`PID`, `S1ID`, `S2ID`, `S3ID`) VALUES ('3', '0', '0', '12');

SELECT PID, '01' SID,S1ID PNUM FROM product_store_count_2 WHERE S1ID>0
UNION 
SELECT PID, '02' SID,S2ID PNUM FROM product_store_count_2 WHERE S2ID>0
UNION
SELECT PID, '03' SID,S3ID PNUM FROM product_store_count_2 WHERE S3ID>0
ORDER BY PID,SID ASC