是否可以在 T-SQL 中将行转换为可变数量的列?

2021-09-10 00:00:00 sql tsql sql-server

我有一个奇怪的要求，我需要将可变数量的行转换为列.我需要帮助确定这是否可以在 SQL 中完成，或者我是否应该只用不同的语言编写程序.

I have a strange requirement where I need to convert a variable amount of rows into columns. I need help figuring out if this could be done in SQL, or if I should just write a program in a different language.

假设我有一个表 Clients，它只保存最少的客户端数据.然后是一个名为 Attributes 的表，它命名了不同的可能属性(例如，电话号码、地址等).最后，我有第三个表 ClientAttributes，其中包含两个 FK 和值.

Let's assume I have a table Clients, which only holds minimal client data. Then, a table called Attributes, which names the different possible attributes (say, Phone number, address, etc). Finally, I have the third table, ClientAttributes which holds the two FKs and the value.

因此，对于每个客户端，我都有任意数量的属性.客户端可以有零个、1 个或无限个电话号码、零个、1 个或无限个地址，等等.

So for each client I have any number of attributes. A client can have zero, 1 or infinite phone numbers, zero, 1, or infinite addresses, and so on.

我需要的是所有数据的表格视图.客户名称、电话、电话 2、电话 3、...、地址、地址 2、地址 3.... 等等.如果客户端没有这样的值，则该值将为空.显然这意味着每次执行查询时列数可能不同.

What I need is a table view of all that data. Client Name, Phone, Phone 2, Phone 3, ..., Address, Address 2, Address 3.... and so on. If a client has no such value, the value will be null. Obviously this means that the number of columns may be different every time the query is executed.

这需要与 SQL Server 2008 兼容.

This needs to be compatible as far back as SQL Server 2008.

这可以完全在 T-SQL 中完成，还是应该通过转储数据并让 C# 处理它来完成这个客户端?我可以在 C# 中轻松完成，但我不确定这是否可以在 SQL 中完成.SQL 将是首选，因为数据集可能太大而无法放入 RAM.

Could this be done purely in T-SQL, or should do this client-side by just dumping the data and let C# handle it? I could easily do it in C# but I'm not sure this could be done in SQL. SQL would be preferred because the dataset may be too large to fit in RAM.

推荐答案

如果需要，这可以通过动态 sql 在 SQL 中完成.为一个项目(我将使用电话)执行此操作的基本理论如下，您可以为所需的其他列分组重复此操作.请注意，没有人会说它很漂亮.

This can be done in SQL through dynamic sql, if you need to. The basic theory for doing it for one item (I'll use phone) is as follows, you'd repeat this for each other grouping of columns you want. Note that no one will ever say that it is pretty.

  • 创建一个基本查询(CTE、TEMP 表等)，获取每个有效电话号码的客户端 ID、电话.添加行号ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY (whatever))" - 我将其称为 basedata
  • 从 basedata 中获取最大 row_number - 这是您需要的电话列数

通过从 i = 1 循环到 MaxRowNo 来制作动态 SQL 查询字符串的一部分.在每个循环中，您构建一个选择字符串和一个连接字符串.选择字符串应在每个循环中添加如下内容

Make parts of a dynamic SQL query string by looping from i = 1 to MaxRowNo. In each loop, you build up a selection string, and a join string. The selection string should add something like the following in each loop

Set @SelectStr = @SelectStr + 'P' + cast(i as varchar(10)) + '.Phone,';

连接字符串应该在每个循环中添加类似这样的东西

The join string should add something like this in each loop

Set @JoinStr = @JoinStr + ' left outer join baseData P' + cast(i as varchar(10)) + ' on P' + cast(i as varchar(10)) + '.ClientID = C.ClientID and P' + cast(i as varchar(10)) + '.RowNo = ' + cast(i as varchar(10));

您可以对地址和任何其他重复的列组重复上述整个过程 - 确保不要在别名上加倍.然后，您将通过添加查询的任何固定的、不变的部分(客户端数据)来组成最终的动态 sql 查询，就像这样

You would repeat the whole above process for addresses, and any other repeating groups of columns - make sure you don't double up on alias names. Then you would make up your final dynamic sql query by adding any fixed, unchanging parts of the query (client data), something like this

Set @FinalQuery = 'SELECT C.ClientID, C.ClientName, ' + @SelectStr + ' From Client C ' + @JoinStr

您最终建立的查询(假设最多有三个电话和两个地址作为示例)将如下所示 - 然后执行此字符串

Your final built up query (assuming there is max three phones and two addresses as an example) would look something like this - then EXEC this string

SELECT C.ClientID, C.ClientName, --any other client stuff you need here
   P1.Phone, P2.Phone, P3.Phone, A1.Address, A2.Address
From Client C
   left outer join baseData P1 on P1.ClientID = C.ClientID and P1.RowNo = 1
   left outer join baseData P2 on P2.ClientID = C.ClientID and P2.RowNo = 2
   left outer join baseData P3 on P3.ClientID = C.ClientID and P3.RowNo = 3
   left outer join baseAddr A1 on A1.ClientID = C.ClientID and A1.RowNo = 1
   left outer join baseAddr A2 on A2.ClientID = C.ClientID and A2.RowNo = 2

相关文章