У меня есть две таблицы: Стандарты и Предложения услуг. Стандарт может иметь несколько Предложений услуг. Каждый Стандарт может иметь различное количество Предложений услуг, связанных с ним.
То, что я должен смочь сделать, записать представление, которое возвратит некоторые общие данные и затем перечислит предложения услуг на одной строке. Например:
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 в этом случае), таким образом, мы не можем превысить мимо этого.
Какие-либо идеи о том, как приблизиться к этому?
Место, которое я запустил, ниже. Это просто возвратило несколько строк для каждого Предложения услуг, когда они должны быть на одной строке.
SELECT *
FROM SERVICE_OFFERINGS
WHERE STANDARD_KEY IN (SELECT STANDARD_KEY
FROM STANDARDS)
Дополнительный SQL
Таким образом, вот SQL, я имею, который возвращает все, что я хочу, но возвращу 11 строк из-за того, чтобы там быть 11 Предложениями услуг. Я пробовал сводную таблицу и, может казаться, не понимаю это с этим. Кто-то может помочь с примером кода?
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
ОБНОВЛЕНИЕ
Так как база данных не предполагает Сводные таблицы (и не мог выяснить предложение XML), я должен был сделать немного хитрого SQL, чтобы заставить это работать. Вот то, что я использовал:
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.
Ознакомьтесь с http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData
Вместо PIVOT следует использовать комбинацию FOR XML и SplitToColumns.
Используйте FOR XML
и перенесите свои предложения в один столбец Объединение значений строк в Transact-SQL
Затем используйте функцию стиля CTE, чтобы разбить одну ячейку на столбцы, как показано здесь http://www.sqlservercentral.com/articles/CTE/67974/
Это даст вам таблицу, развернутую нужным вам образом.
Затем выполните арифметические действия, чтобы подсчитать количество ненулевых столбцов, и в конце у вас будет нужный счет.
Да, вам нужно использовать сводные запросы.
21 столбец всегда один и тот же, или вы можете показать не более 21 столбца (из, скажем, сотен)? Если фактические столбцы могут отличаться от запроса к запросу, вам придется подумать о написании динамических запросов (построить запрос в виде строки с включением столбцов, которые нужно повернуть, а затем выполнить строку).
Филипп прав. Если у вас всегда будет 21 столбец, это простой запрос Pivot. Я вставляю сюда образец кода, который вы можете использовать. Но если количество столбцов будет варьироваться от 1 до 21, вам придется написать динамический запрос.
SELECT standard_key, stabdard_name, [A] as SO1, [B] as SO2, [C] as SO3, [D] as SO4, [E] as SO5....-- and so on with the other columns
FROM
(SELECT ST.standard_key, ST.stabdard_name, SO.service_offering_id
FROM SERVICE_OFFERINGS SO
INNER JOIN STANDARDS ST
ON SO.standard_key= ST.standard_key)p
PIVOT
(
MAX (service_offering_id)
FOR service_offering_id IN
( [A], [B], [C], [D], [E]....-- and so on with the other values)
) AS pvt
ORDER BY standard_key
Если столбцы могут отличаться, вы можете попробовать что-нибудь вроде этого:
declare @sql nvarchar(max)
declare @sql2 nvarchar(max)
SET @sql2=''
set @sql = '
select
standard_key, stabdard_name,'
select @sql = @sql + '['+ service_offering_id + '] AS [SO' + convert(varchar, Row_number() OVER (ORDER BY service_offering_id))+ '],'
from (select distinct [service_offering_id] from [SERVICE_OFFERINGS]) as moduleids
select @sql2 = @sql2 + '['+ service_offering_id + '],'
from (select distinct [service_offering_id] from [SERVICE_OFFERINGS]) as moduleids
set @sql2 = substring(@sql2,1,len(@sql2)-1)
set @sql = substring(@sql,1,len(@sql)-1) + '
FROM
(SELECT ST.standard_key, ST.stabdard_name, SO.service_offering_id
FROM SERVICE_OFFERINGS SO
INNER JOIN STANDARDS ST
ON SO.standard_key= ST.standard_key)p
PIVOT
(
MAX (service_offering_id)
FOR service_offering_id IN
(' + @sql2 +
')) AS pvt
ORDER BY standard_key'
print @sql
exec sp_executesql @sql