SQL查询以传输数据

Question

我在SQL Server 2008中的下表：

ID  Type    Value
6   A_Left  A
6   B_Left  B
6   C_Left  C
6   D_Left  D
6   E_Left  E
6   A_Right A
6   B_Right B
6   C_Right C
6   D_Right D
6   E_Right E

我需要根据值列将它们分组，例如：

ID  Type    Type    Value
6   A_Left  A_Right A
6   B_Left  B_Right B
6   C_Left  C_Right C
6   D_Left  D_Right D
6   E_Left  E_Right E

这可能吗？我没有关于这种转台的涉及Google的确切条款。

Answer 1

使用INNER JOIN，将表与自身连接。在联接的一侧，限制到匹配的_Left，另一侧限制到匹配_Right的那些。

SELECT
  l.ID,
  l.Type AS L_Type,
  r.Type AS R_Type,
  l.Value
FROM
  t l
  INNER JOIN t r ON l.ID = r.ID AND l.Value = r.Value
WHERE
  RIGHT(l.type, 4) = 'Left'
  AND RIGHT(r.type, 5) = 'Right'

这是一个演示：http：//sqlfiddle.com/#！6/f6590/4

另外，您可以使用LIKE而不是RIGHT()字符串函数：

SELECT
  l.ID,
  l.Type AS L_Type,
  r.Type AS R_Type,
  l.Value
FROM
  t l
  INNER JOIN t r ON l.ID = r.ID AND l.Value = r.Value
WHERE
  l.type LIKE '%_Left'
  AND r.type LIKE '%_Right'

http://sqlfiddle.com/#！6/f6590/5

Answer 2

使用patindex：

SELECT t1.ID, t1.Type As Type, t2.Type AS Type, t1.Value As Value 
FROM t as t1 JOIN t t2 ON t1.ID=t2.ID AND t1.Value=t2.Value
WHERE PATINDEX('%_Left',t1.Type)>0 
AND PATINDEX('%_Right',t2.Type)>0

类似于上面但使用cte和喜欢（也可以使用patindex）：

WITH CTE AS
(
    Select ID,Type,Value FROM t WHERE Type LIKE '%_Left'
)
SELECT t1.ID,CTE.Type+ ',' + t1.Type As Type,t1.Value FROM CTE
JOIN t t1
ON t1.ID = CTE.ID AND t1.Value = CTE.Value 
WHERE  t1.Type LIKE '%_Right'