問題描述

我有兩個表:標準和服務產品.一個標準可以有多個服務產品.每個標準可以關聯不同數量的服務產品.

I have two tables: Standards and Service Offerings. A Standard can have multiple Service Offerings. Each Standard can have a different number of Service Offerings associated to it.

我需要做的是編寫一個視圖，該視圖將返回一些常見數據，然后在一行中列出服務產品.例如:

What I need to be able to do is write a view that will return some common data and then list the service offerings on one line. For example:

Standard Id | Description | SO #1 | SO #2 | SO #3 | ... | SO #21 | SO Count
1           | One         | A     | B     | C     | ... | G      |  21
2           | Two         | A     |       |       | ... |        |  1
3           | Three       | B     | D     | E     | ... |        |  3

我不知道如何寫這個.SO 列的數量設置為特定數量(在本例中為 21)，因此我們不能超過該數量.

I have no idea how to write this. The number of SO columns is set to a specific number (21 in this case), so we cannot exceed past that.

關于如何解決這個問題的任何想法?

Any ideas on how to approach this?

我開始的地方在下面.當需要在一行上時，它只是為每個服務產品返回多行.

A place I started is below. It just returned multiple rows for each Service Offering, when they need to be on one row.

SELECT *
  FROM SERVICE_OFFERINGS
 WHERE STANDARD_KEY IN (SELECT STANDARD_KEY
                          FROM STANDARDS)

附加 SQL

這里是我擁有的 SQL，它返回我想要的所有內容，但由于有 11 個服務產品，將返回 11 行.我一直在嘗試數據透視表，但似乎無法解決這個問題.有人可以幫忙提供代碼示例嗎?

So here is the SQL I have that returns everything that I want, but will return 11 rows due to there being 11 Service Offerings. I have been trying the pivot table and can't seem to figure it out with this. Can someone help with a code example?

SELECT DISTINCT stpc.standard_key,
                stpc.test_id,
                NULL AS pricebook_id,
                stpc.stabdard_name AS description,
                stpc.date_start AS begin_date,
                stpc.date_end AS end_date,
                sopd.service_offering_id
  FROM STANDARDS stpc,
       SERVICE_OFFERINGS sopd
 WHERE 1=1
   AND sopd.standard_key = stpc.standard_key
 ORDER BY stpc.standard_key, sopd.service_offering_id

更新

由于數據庫不假設 PIVOT 表(并且無法找出 XML 建議)，我不得不執行一些棘手的 SQL 以使其工作.這是我使用的:

Since the database does not suppose PIVOT tables (and couldn't figure out the XML suggestion), I had to do a little tricky SQL to get it to work. Here is what I used:

select stpc.oracle_product_code AS test_id,
       CASE WHEN stpc.store_key = 200 THEN 'CE_USAUSD09'
            WHEN stpc.store_key = 210 THEN 'CE_CANCAD09' END AS pricebook_id,
       stpc.standard_name AS its_test_desc,
       CONVERT(VARCHAR(10), stpc.date_start, 101) AS begin_date,
       CONVERT(VARCHAR(10), stpc.date_end, 101) AS end_date,
       MAX(CASE WHEN rn = 1 THEN b.service_offering_id END) AS SERVICE_OFFERING_1,
       MAX(CASE WHEN rn = 2 THEN b.service_offering_id END) AS SERVICE_OFFERING_2,
       MAX(CASE WHEN rn = 3 THEN b.service_offering_id END) AS SERVICE_OFFERING_3,
       MAX(CASE WHEN rn = 4 THEN b.service_offering_id END) AS SERVICE_OFFERING_4,
       MAX(CASE WHEN rn = 5 THEN b.service_offering_id END) AS SERVICE_OFFERING_5,
       MAX(CASE WHEN rn = 6 THEN b.service_offering_id END) AS SERVICE_OFFERING_6,
       MAX(CASE WHEN rn = 7 THEN b.service_offering_id END) AS SERVICE_OFFERING_7,
       MAX(CASE WHEN rn = 8 THEN b.service_offering_id END) AS SERVICE_OFFERING_8,
       MAX(CASE WHEN rn = 9 THEN b.service_offering_id END) AS SERVICE_OFFERING_9,
       MAX(CASE WHEN rn = 10 THEN b.service_offering_id END) AS SERVICE_OFFERING_10,
       MAX(CASE WHEN rn = 11 THEN b.service_offering_id END) AS SERVICE_OFFERING_11,
       MAX(CASE WHEN rn = 12 THEN b.service_offering_id END) AS SERVICE_OFFERING_12,
       MAX(CASE WHEN rn = 13 THEN b.service_offering_id END) AS SERVICE_OFFERING_13,
       MAX(CASE WHEN rn = 14 THEN b.service_offering_id END) AS SERVICE_OFFERING_14,
       MAX(CASE WHEN rn = 15 THEN b.service_offering_id END) AS SERVICE_OFFERING_15,
       MAX(CASE WHEN rn = 16 THEN b.service_offering_id END) AS SERVICE_OFFERING_16,
       MAX(CASE WHEN rn = 17 THEN b.service_offering_id END) AS SERVICE_OFFERING_17,
       MAX(CASE WHEN rn = 18 THEN b.service_offering_id END) AS SERVICE_OFFERING_18,
       MAX(CASE WHEN rn = 19 THEN b.service_offering_id END) AS SERVICE_OFFERING_19,
       MAX(CASE WHEN rn = 20 THEN b.service_offering_id END) AS SERVICE_OFFERING_20,
       MAX(CASE WHEN rn = 21 THEN b.service_offering_id END) AS SERVICE_OFFERING_21,
       MAX(rn) AS service_offering_count
FROM (
select standard_key,
       service_offering_id, 
       row_number() over (partition by standard_key order by standard_key) rn
from SERVICE_OFFERINGS
) B,
SERVICE_OFFERINGS sopd,
STANDARDS stpc
where b.service_offering_id = sopd.service_offering_id
AND b.standard_key = stpc.standard_key
AND sopd.standard_key = stpc.standard_key
AND stpc.store_key IN (200,210)
AND stpc.create_date > '03/29/2010'
group by stpc.oracle_product_code,stpc.store_key,stpc.standard_name,stpc.date_start,stpc.date_end

推薦答案

您可以為此使用 PIVOT 功能.

Check out http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

您應該使用 FOR XML 和 SplitToColumns 的組合來代替 PIVOT.

Instead of PIVOT, you should use a combination of FOR XML and SplitToColumns.

使用 FOR XML 并將您的產品轉出到單列 在 Transact-SQL 中連接行值

Use FOR XML and pivot out your Offerings to a single column Concatenating Row Values in Transact-SQL

然后使用 CTE 樣式函數將單個單元格分解為列，如下所示 http://www.sqlservercentral.com/articles/CTE/67974/

Then use a CTE style function to break down a single cell into columns as shown here http://www.sqlservercentral.com/articles/CTE/67974/

這將為您提供一個以您需要的方式旋轉的表格.

This will give you a table pivotted out in the fashion that you need.

然后進行算術運算以獲得非空列的計數，最后得到所需的計數.

Then do arithmetic to get a count of non-null columns and you have the count you need at the end.

這篇關于選擇返回動態列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！