连接来自同一表的两个选择查询 [英] Joining two select queries from the same table

问题描述

该表包含一个ID列，valueHeading列和一个value列.我想根据值具有哪种valueHeading类型将value列分为两个新列，分别称为valueHeading1和valueHeading2.

The table contains an ID column, valueHeading column and a value column. I want to separate the value column into two new columns called valueHeading1 and valueHeading2 depending on which type of valueHeading the value has.

所以我想加入这个选择: 完全加入

So I want to join this select: Full join

SELECT ID
      ,valueHeading
      ,value as 'valueHeading1'
FROM table1
WHERE valueHeading = 'valueHeading1'

使用此选择:

SELECT ID
      ,value as 'valueHeading2'
FROM table1
WHERE valueHeading = 'valueHeading2'

各自的ID.我该怎么做?

on their respective ID's. How do I do this?

编辑以说明我要执行的操作:

Edit to illustrate what I want to do:

原始表:

ID    valueHeading    value
0     valueHeading1    a
0     valueHeading2    a
1     valueHeading1    ab
1     valueHeading2    NULL
2     valueHeading1    abcd
2     valueHeading2    abc

新表格:

ID    valueHeading1    valueHeading2
0          a               a
1          ab              NULL
2         abcd             abc

推荐答案

在SQLServer2005 +中可能使用PIVOT

In SQLServer2005+ possible use PIVOT

SELECT ID, valueHeading1, valueHeading2
FROM
(
 SELECT *
 FROM dbo.test28
 WHERE valueHeading IN ('valueHeading1', 'valueHeading2')
 ) x
 PIVOT
 (
  MAX(value)
  FOR valueHeading IN ([valueHeading1], [valueHeading2])
  ) p

在 SQLFiddle

这篇关于连接来自同一表的两个选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！