SQL移调行
问题描述:
柱
我已经从一个表中的以下样品SQL移调行
+-------------+---+------------------+--+
| Heading1 | 2 | 3 | |
+-------------+---+------------------+--+
| Cats | d | 1 | |
| Cats | e | 13/01/2000 12:29 | |
| Dogs | s | 1 | |
| Dogs | e | 13/01/2000 12:29 | |
| Frogs | d | 1 | |
| Frogs | e | 13/01/2000 12:29 | |
| Cows | d | 1 | |
| Cows | e | 13/01/2000 12:29 | |
+-------------+---+------------------+--+
我想列2的行是不同的标题和含有一排用于从图1个3 A和呈现这样的每个唯一值:
+-----------+------+------------------+------+
| Headings: | d | e | s |
+-----------+------+------------------+------+
| Cats | 1 | 13/01/2000 12:29 | Null |
| Dogs | Null | 13/01/2000 12:29 | 1 |
| Frogs | 1 | 13/01/2000 12:29 | Null |
| Cows | 1 | 13/01/2000 12:29 | Null |
+-----------+------+------------------+------+
任何想法GOOGLE了它,并与这一个:)
EDIT失去意志:我没有提到的事实是什么成为列在这个CAS动态即e d,e,s可以是任何东西,真的很感谢答案。
感谢所有
答
可以移调两种rows to columns
方式
方法1:Conditional Aggregate
SELECT Heading1,
Max(CASE WHEN [2] = 'd' THEN [3] END) 'd',
Max(CASE WHEN [2] = 'e' THEN [3] END) 'e',
Max(CASE WHEN [2] = 's' THEN [3] END) 's'
FROM <tablename>
GROUP BY Heading1
方法2:Pivot
SELECT *
FROM (SELECT *
FROM <tablename>) a
PIVOT (Max([3])
FOR [2] IN ([d],
[e],
[s])) piv
答
SELECT Heading1,
(SELECT Heading3 FROM Table2 b WHERE a.Heading1 = b.Heading1 and b.Heading2 = 'd') as d,
(SELECT Heading3 FROM Table2 b WHERE a.Heading1 = b.Heading1 and b.Heading2 = 'e') as e,
(SELECT Heading3 FROM Table2 b WHERE a.Heading1 = b.Heading1 and b.Heading2 = 's') as s
INTO Table2
FROM Table1 a
GROUP BY Heading1
为什么downvote查询时,不正是需要什么? – CrimsonKing 2014-12-03 19:17:27