行到列SQL Server查询 [英] Rows to columns SQL Server query

问题描述

我正在编写一个将在.NET应用程序中使用的查询，因此我希望SQL Server 2008 o为我做很多处理，而不是为应用程序将在其上运行的客户端PC进行处理.

I am writing a query that will be used in a .NET application, therefore I would like the SQL Server 2008 o do much of the processing for me instead of the client PC that the application will run on.

我正在尝试从具有索引值的单个表中获取数据，基于该索引值，我希望将该项目放置在特定列中.

I am trying to get data from a single table with an index value, based off of that index value I would like that item to placed in a specific column.

这里是一个例子:

表MAS:

MA      SN      Mindex
B275    7A1515  1
B276    7A1515  2
E530    7A1515  3
B291    7A1519  1
B292    7A1519  2
E535    7A1519  3
B301    7A2515  1
B302    7A2515  2
B331    7A2519  1
B332    7A2519  2

这是我想要的输出:

 SN      mi1     mi2     mi3
 7A1515  B275    B276    E530
 7A1519  B291    B292    E535
 7A2515  B301    B302    null
 7A2519  B331    B332    null

我尝试执行以下查询，该查询可用于具有3个索引的项目，但如果只有两个，则会使用随机数据填充它.

I tried doing the following query, it works with items with 3 indexes but if there are only two, it fills it with random data.

 select mas1.SN, mas1.MA as mi1,mas2.MA as mi2, mas3.MA as mi3
 from MAS ma1, MAS ma2, MAS ma3
 where mas1.SN = '7A1515' and mas1.Mindex = '1' and mas2.Mindex = '2' and mas3.Mindex = '3'

我想知道是否有人能够指出正确的方向，因为我在编写这些高级查询时还很陌生.

I was wondering if anyone would be able to point me in the right direction as I am still fairly new at writing these advanced queries.

推荐答案

就像在SQL Server 2008上一样，您也可以使用PIVOT，但是使用IMO的旧方法通常更容易.

As you are on SQL Server 2008 you can also use PIVOT but the old style way of doing it is often easier IMO.

SELECT 
    SN,
    MAX(CASE WHEN Mindex=1 THEN MA END) AS mi1,
    MAX(CASE WHEN Mindex=2 THEN MA END) AS mi2,
    MAX(CASE WHEN Mindex=3 THEN MA END) AS mi3
FROM MAS
GROUP BY SN     

这篇关于行到列SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！