問題描述

我有一個奇怪的要求，我需要將可變數量的行轉換為列.我需要幫助確定這是否可以在 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

這篇關于是否可以在 T-SQL 中將行轉換為可變數量的列?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！