在sql中将行转换为列

Question

如何查询得到以下结果。。。？！！

表1:

MainID col1 col2
1       qq    qq
2       qq    qq
3       qq    qq
4       qq    qq
5       qq    qq

表2

MainID      lineNo      Text
1           1           price
1           2           name
1           3           date
2           1           price
2           2           name
2           3           date

我需要一个类似的查询结果

MainId     Col1    col2     price   name    date
1           qq      qq      price   name    date
2           qq      qq      price   name    date

这需要在MainID的3个不同条件下制作3个不同的列；形成单行。

Answer 1

您应该能够通过连接多个嵌入式查询来完成以下操作：

Select 
      table1.MainID, table1.col1, table1.col2, q1.price, q2.name, q3.date
from 
table1 
left outer join (select 
                 MainID, lineNo, Text as price 
                 from 
                 table2) q1 on table1.MainID = q1.MainID
left outer join (select 
                 MainID, lineNo, Text as name 
                 from table2) q2 on table1.MainID = q2.MainID
left outer join (select 
                 MainID, lineNo, Text as date 
                 from table2) q3 on table1.MainID = q3.MainID

Answer 2

这是一个透视查询。然而，还有其他方法可以解决这个问题

select t1.mainid, t1.col1, t1.col2, tprice.text as price, 
       tname.text as name, tdate.text as date
from table1 t1 left join
     table2 tprice
     on t1.mainid = tprice.mainid and t2.lineno = 1 left join
     table2 tname
     on t1.mainid = tname.mainid and t2.lineno = 2 left join
     table2 tdate
     on t1.mainid = tdate.mainid and t2.lineno = 3;

您也可以通过条件聚合和使用pivot关键字来完成此操作。