如何从SQL中的多行和（重复）coulumns中选择一行 [英] How to select one row from multiple rows and (repeated) coulumns in SQL

问题描述

我有一个样本表：
 
 ID T_art Z_art F_ID 
 
 2101 sw 2 
 2101 az 1 
 2101 sr 3 
 2102 sw 4 
 2102 az 5 
 
 
i希望得到如下结果:(按F_ID排序）
 
 ID T_art Z_art T_art Z_art T_art Z_art 
 
 2101 azswsr 
 2102 swaz 

我有什么尝试过：



有没有办法在sql中实现这一点？非常感谢。非常感谢。

谢谢。

解决方案

第一件事就是单个SQL查询返回一个固定的数字列。这意味着你要么必须知道结果中的列数并根据它来构建查询，要么在获取信息后需要动态构建查询，需要多少列。



在这两种情况下，一种方法是利用PIVOT。看看下面的例子：

- 在SQL查询中使用Pivot的简单方法 [ ^ ]

- SQL Server中的动态数据透视查询 [ ^ ]



ADDITION

如果我理解正确的话您描述的逻辑，您可以使用外连接在查询中简单地包含表8次。例如

  SELECT  a.fk_PordId，
 a.fk_Transport，
 a.fk_Destination，
 b.fk_Transport，
 b.fk_Destination，
 c.fk_Transport，
 c.fk_Destination 
  -   继续完成8个表格 
  FROM  MyTableName a 
  LEFT   OUTER   JOIN  MyTableName b 
  ON （b.fk_PordId = a.fk_PordId 
  AND  b.fk_id> a。 fk_id 
  AND   NOT   EXISTS  （ SELECT   1  
  FROM  MyTableName b1 
  WHERE  b1.fk_PordId = b.fk_PordId 
  AND  b1.fk_id> a.fk_id 
  AND  b1.fk_id< b.fk_id））
  LEFT   OUTER  加入 MyTableName c 
  ON （c.fk_PordId = b.fk_PordId 
  AND  c.fk_id> b.fk_id 
  AND   NOT   EXISTS （ SELECT   1  
  FROM  MyTableName c1 
  WHERE  c1.fk_PordId = c.fk_PordId 
  AND  c1.fk_id> b.fk_id 
  AND  c1.fk_id< c.fk_id））
   -   继续完成8个表 
  WHERE   NOT   EXISTS （ SELECT   1  
  FROM  MyTableName a1 
  WHERE  a1.fk_PordId = a.fk_PordId 
  AND  a1.fk_id< a.fk_id）

i have a sample Table :

ID      T_art   Z_art  F_ID

2101    s       w      2
2101    a       z      1
2101    s       r      3
2102    s       w      4
2102    a       z      5


i want the result set like this:(Order By F_ID)

ID     T_art    Z_art  T_art    Z_art     T_art   Z_art

2101   a         z       s        w         s       r
2102   s         w       a        z

What I have tried:

is there any way to achieve this in sql? it's urgent.any help is really appreciated.
Thank You.

解决方案

The first thing is that a single SQL query returns a fixed number of columns. This means that you either have to know the column count in the result and build the query based on that or you need to build the query dynamically after fetching the information, how many columns you need.

In both cases one way is to utilize PIVOT. Have a look at the following examples:
- Simple Way To Use Pivot In SQL Query[^]
- Dynamic Pivot Query in SQL Server[^]

ADDITION
If I understand correctly the logic you described, you can simply include the table 8 times in your query using outer join. For example something like

SELECT a.fk_PordId, 
       a.fk_Transport, 
       a.fk_Destination,
       b.fk_Transport, 
       b.fk_Destination,
       c.fk_Transport, 
       c.fk_Destination
       -- continue to complete 8 tables
FROM MyTableName a
LEFT OUTER JOIN MyTableName b 
   ON (    b.fk_PordId = a.fk_PordId 
       AND b.fk_id     > a.fk_id  
       AND NOT EXISTS (SELECT 1 
                       FROM MyTableName b1 
                       WHERE b1.fk_PordId = b.fk_PordId 
                       AND   b1.fk_id     > a.fk_id 
                       AND   b1.fk_id     < b.fk_id))
LEFT OUTER JOIN MyTableName c
   ON (    c.fk_PordId = b.fk_PordId
       AND c.fk_id > b.fk_id
       AND NOT EXISTS (SELECT 1 
                       FROM MyTableName c1 
                       WHERE c1.fk_PordId = c.fk_PordId 
                       AND   c1.fk_id     > b.fk_id
                       AND   c1.fk_id     < c.fk_id))
-- continue to complete 8 tables
WHERE NOT EXISTS (SELECT 1 
                  FROM MyTableName a1 
                  WHERE a1.fk_PordId = a.fk_PordId
                  AND   a1.fk_id     < a.fk_id)

这篇关于如何从SQL中的多行和（重复）coulumns中选择一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！