SQL, превращающий строки в столбцы

#sql #join #rows

#sql #Присоединиться #строки

Вопрос:

Я не совсем уверен, возможно ли то, что мне нужно. У меня есть две таблицы, которые объединены, одна из которых представляет собой список элементов, а другая — когда для этих элементов было какое-либо перемещение на указанную дату. Перемещения задаются сумматорами, в этом примере 1 = Куплено, 2 = Продано, 3 = Скорректировано. Кодирование, которое у меня есть до сих пор, дает мне отдельную строку для каждого сумматора. Мне нужно объединить строки в одну для каждого элемента.

 SELECT [Totalizer]=COALESCE(t2.[F1034],0)
      ,[UPC]=t1.[F01]
      ,[QTY]=sum(coalesce(t2.[F64],0))
      ,[Total Amount]=sum(COALESCE(t2.[F65],0))
FROM [STORESQL].[dbo].[COST_TAB] t1
LEFT OUTER JOIN [STORESQL].[dbo].[RPT_ITM_D] t2 
ON t1.F01=t2.F01 AND (F254='2011-10-1') and (F1034=1 or F1034=2 or F1034=3)
group by t1.F01,F1034
order by t1.F01

Таблица COST_TAB состоит из:

 UPC
1
2
3
4

RPT_ITM_D состоит из перемещения элемента:

 UPC     Date       Totalizer     QTY     Total Amount
1       2011-10-1  1             1       9.00
1       2011-10-1  2             1       9.99
2       2011-10-1  1             2       6.00
2       2011-10-1  2             1       3.99
2       2011-10-1  3             1       3.00
3       2011-10-1  1             1       1.00

Код SQL, который у меня есть сейчас, приводит к:

 UPC     Date       Totalizer     QTY     Total Amount
1       2011-10-1  1             1       9.00
1       2011-10-1  2             1       9.99
2       2011-10-1  1             2       6.00
2       2011-10-1  2             1       3.99
2       2011-10-1  3             1       3.00
3       2011-10-1  1             1       1.00
4       2011-10-1  0             0       0.00

Мне нужно, чтобы это привело к:

 UPC     Date       Purchased  AMT   Sold  AMT    Adjusted  AMT
1       2011-10-1  1          9.00  1     9.99   0         0.00
2       2011-10-1  2          6.00  1     3.99   1         3.00
3       2011-10-1  1          1.00  0     0.00   0         0.00
4       2011-10-1  0          0.00  0     0.00   0         0.00

Я понимаю, что мне, вероятно, придется полностью переработать свои столбцы, но я не знаю, с чего начать, и смогу ли я вообще это сделать.

1. поля с именами F1034, F64 и F65? Я надеюсь, что это имеет смысл в вашем контексте, а не философия дизайна какого-то пещерного человека, никогда не видевшего дневного света администратора базы данных.

2. Это какой-то метод организации розничной торговли .. Его использует наша база данных магазина.. Большую часть времени это ЛАВАШ..

Ответ №1:

Предполагая, что вы используете MS SQL Server, вы можете использовать PIVOT для достижения этой цели — объяснение и некоторые примеры см.http://msdn.microsoft.com/en-us/library/ms177410.aspx и http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

Другой вариант — использовать подвыборки:

 SELECT
T1.F01 AS UPC,
T2.F254 AS TheDate,
(SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 1 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS PURCHASED,
(SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 1 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS AMT_P,
(SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 2 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS SOLD,
(SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 2 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS AMT_S,
(SELECT SUM (COALESCE (X.F64, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 3 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS ADJUSTED,
(SELECT SUM (COALESCE (X.F65, 0)) FROM [STORESQL].[dbo].[RPT_ITM_D] X WHERE X.F1034 = 3 AND X.F01 = T2.F254 AND X.F01 = T1.F01) AS AMT_A
FROM [STORESQL].[dbo].[COST_TAB] t1
LEFT OUTER JOIN [STORESQL].[dbo].[RPT_ITM_D] t2 
ON t1.F01=t2.F01 AND (F254='2011-10-1') and (F1034=1 or F1034=2 or F1034=3)
group by T1.F01, T2.F254
ORDER BY T1.F01, T2.F254

Вы должны сравнить оба варианта относительно производительности / плана выполнения.

1. Мне пришлось внести в него некоторые изменения, но после этого все работало отлично. Некоторые изменения заключались в том, чтобы объединить перед выбором и исправить X.F01 = T2.F254.. Спасибо

Ответ №2:

В вашем пространстве имен schema / базе данных вы найдете определение вашей таблицы. Точная таблица, в которой хранятся определения таблиц, зависит от того, какую СУБД вы используете. Например, information_schema.СТОЛБЦЫ будут содержать эту информацию для вас в MYSQL, и вы можете выбрать ее оттуда, как и из любой другой таблицы, и скрестить эти возвращаемые значения в соединении с вашей таблицей.