Вопрос по sql-server, sql-server-2008 – Объединить несколько строк из нескольких таблиц

2

Я рассмотрелмного Другой посты здесь и стали довольно знакомыми с функцией Coalesce, но я не смог понять, как выполнить эту конкретную задачу.

Итак, у меня есть таблица комиссий и таблица категорий. Я создалсуть здесь так что вы можете увидеть точную структуру данных с некоторыми примерами данных. В основном, таблица Комиссии имеет столбцы SalesRepID, LocationID, CategoryID, SurgeonID и CommissionPercent.

Использование функции CoalesceЯ смог получить что-то вроде этого, передав SalesRepID, LocationID и SurgeonID:

<code>.05 (Shirts), .05 (Shoes), .05 (Dresses), .10 (Hats), .15 (Pants)
</code>

However, I'm trying to get it to look like:

<code>.05 (Shirts, Shoes, Dresses), .10 (Hats), .15 (Pants)
</code>

Я пробовал это несколько раз с STUFF, но так и не получил результат, который искал.

Что заставляет меня спросить, возможно ли это вообще в MsSQL 2008 R2? Если это так, то любая помощь в получении результата, который я ищу, будет принята с благодарностью.

Большое спасибо за ваше время и энергия,

Эндрю

Ваш Ответ

2   ответа
3

чем тянуть зубы, чтобы получить схему и данные. :-) Если вы включите это в свой основной запрос, вы должны увидеть результаты, которые вы ищете (ну, очень близко - см. Ниже).

DECLARE @SalesRepID INT, @SurgeonID INT, @LocationID INT;
SELECT @SalesRepID = 2, @SurgeonID = 1, @LocationID = 1;

;WITH x AS 
(
  SELECT CommissionPercent, Categories = STUFF((SELECT ', ' 
      + tCat.Category FROM #tCategories AS tCat 
      INNER JOIN #tCommissions AS tCom 
      ON tCat.CategoryID = tCom.CategoryID
      WHERE tCom.CommissionPercent = com.CommissionPercent
      FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
 FROM #tCommissions AS com
 WHERE SalesRepID = @SalesRepID
 AND SurgeonID = @SurgeonID
 AND LocationID = @LocationID
),
y AS
(
  SELECT s = RTRIM(CommissionPercent) + ' (' + Categories + ')' 
  FROM x GROUP BY CommissionPercent, Categories
)
SELECT Result = STUFF((SELECT ', ' + s FROM y 
  FOR XML PATH, TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

Результат немного отличается от того, о котором вы просили, но это можно исправить, применив форматирование строк при извлечении CommissionPercent.

Result
--------------------------------------------------------
0.05 (Shirts, Shoes, Dresses), 0.10 (Hats), 0.15 (Pants)
@AaronBertrand согласен с этим! Но не думаете ли вы (предполагая, что вы обойдете (a) и хотите столкнуться с (b)), используя функцию CLR, это подходящий способ? Я имею в виду, я нахожу запрос немного сложным и нелогичным для такого простого требования.
Да, я все еще нахожу, что CLR заблокирован двумя вещами: (а) заостренными боссами, считающими это опасным, и (б) очень реальной сложностью в развертывании кода. Не только изначально, но и когда вам нужно внести изменения, это может стать очень утомительным упражнением ...
ах хороший ответ за хорошее использование XML вместо CLR. +1
@AaronBertrand Я удивлен, что так быстро получил ответ на эту загадку. Я был в состоянии переместить это в функцию только с одной небольшой настройкой (пришлось переместить, где логика видетьhere). Мне придется пойти домой и изучить это немного больше, чтобы увидеть, чтоreally продолжаю, но еще раз спасибо за ваш быстрый ответ. ajtatum
Я подозреваю, что запрос выглядит более сложным, чем развертывание CLR для кого-то, кто происходит из OO / .NET фоне. Я написал этот запрос за 8 минут. Я все еще буду развертывать библиотеки DLL, если бы я решил сделать это в CLR. Это также та же сложность, когда я перемещаю код на новый сервер (запрос гораздо более переносим), и, как я уже сказал, еще хуже, если мне придется настроить запрос (как я должен был бы сделать в этот случай, потому что я неверно определил положение where). Я не говорю, что CLR - это плохо, но я думаю, что ваше мнение о том, что является более сложным / нелогичным, весьма субъективно.
0

ь ее (без использования курсоров) - создать агрегатную функцию CLR. Вот пример на C # (и на VB):http://technet.microsoft.com/en-us/library/ms131056(v=SQL.90).aspx

Я считаю, что это просто делает то, что вам нужно: объединение.

Комбинируя ваш пример и CLR, чтобы достичь того, чего вы хотите - SQL будет выглядеть так:

SELECT
  c.CommissionPercent
  , dbo.MyAgg(cat.Category)
FROM #tCommissions AS c
JOIN #tCategories AS cat ON c.CategoryID = cat.CategoryID
group by c.CommissionPercent
Спасибо за быстрый ответ. Хотя это не было решением, с которым я столкнулся, я благодарю вас за то, что вы показали мне что-то новое. Я даже не знал об этой возможности. ajtatum

Похожие вопросы