SQL-从联接返回到单列的所有行中的列?

问题描述

我正在编写一个SQL查询，该查询显示交货详细信息，并在单独的列中，用逗号分隔所有交货项目ID.Delivery和DeliveryItems都在单独的表中，所以我想我将使用Join检索链接到该交付的所有项目，但是如何将它们放入要显示的列中，并使用逗号?

谢谢

这是我的表结构的想法

 交付-编号(PK)...(其他非相关领域)...DeliveryItems-编号(PK)交付(链接到交付ID) 

因此，对于该交货的每个项目，我都希望在该交货的列中显示逗号分隔的项目ID.

解决方案

您可以通过在SQL Server 2005中使用PIVOT来实现.PIVOT用于将行转置为列或将列转置为行，因此，在这种情况下，您可以通过使用简单联接来获取行格式的数据，然后可以使用PIVOT进行数据转置，然后可以将列连接为单个列./p>

选中此链接，它是一个很好的例子.

I'm writing an SQL query that displays a deliveries details, plus in a seperate column, all the delivery item ID's seperated by commas. Both Deliveries and DeliveryItems are in a seperate table, so I guess I'll be using a Join to retrieve all items that link to that delivery, but how do I get them into a column I'll be displaying, and seperate each item with a comma?

Thanks

EDIT: Here's an idea of my table structure

Deliveries -
ID (PK)
...(other non relevant fields)...

DeliveryItems -
ID (PK)
Delivery (links to ID of delivery)

So for every item of that delivery, I want to display the item ID comma seperated in a column for that delivery.

解决方案

you can do it by using PIVOT in SQL server 2005.PIVOT is used for Transpose row to column or column to row, so in your case you can get data in row format by using simple join after that you can use PIVOT for to transpose data and then you can concatenate columns in a single column.

check this link, it has a good example.

这篇关于SQL-从联接返回到单列的所有行中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！