Благодарю. это работает для приведенных выше примеров данных, но как бы вы поступили, когда у вас есть больший набор данных?
адали проблему, которую я не уверен, как написать его в SQL SEVER (версия 5/8). Вот простой запрос и его вывод:
Select location, date_time, Item_sold
From Product
Location Date_time Item_sold
VA 12/10/2010 1:30:00 PM Candy
VA 12/10/2010 3:30:00 PM Chips
VA 12/13/2010 12:50:00 AM Wine
DC 12/13/2010 8:00:00 AM Gum
DC 12/13/2010 12:30:00 PM Bags
DC 12/13/2010 1:16:00 PM Cheese
DC 12/13/2010 12:00:00 AM Hotdog
NJ 12/14/2010 12:00:00 AM Coffee
NJ 12/14/2010 1:15:00 PM Beers
NJ 12/14/2010 12:00:00 AM Coffee
NJ 12/14/2010 1:45:00 PM Water
Вот мой желаемый результат, который, я думаю, цикл while / for или функция pivot могли бы сделать работу, но мой опыт еще не достигнут. По сути, мне нужно постепенно увеличивать количество проданных товаров из столбца Item_sold (базовая дата начинается с 12/8 до 12/9, с 12/8 до 12/10, с 12/8 до 12/11, с 12/8 по 12/12 ...)
Location 12/10 to 12/11 12/10 to 12/12 12/10 to 12/13 12/10 to 12/14
VA 2 2 3 3
DC 0 0 3 3
NJ 0 0 0 4
Мне было интересно, если кто-нибудь может edcuate и решить эту проблему. Большое спасибо заранее, Джо
И запрос должен быть динамическим?
Я думаю, что ваши 12/8 - 12/11 и 12/8 - 12/12 должны быть 2 для ВА, не так ли?
@joe - Почему в этом отношении ровно 6 столбцов, укажите ли вы диапазон дат?
@cyberkiwi, да, для Нью-Йорка должен быть ряд, но я показал только VA и DC в качестве примера. диапазон дат от 12/8/10 до 1/5/11.
@joe - можете ли вы подтвердить, что для NJ должна быть строка, а VA не должен быть 0/0/2/2/2/3? Почему 0 между 2 и 3?
Ответы на вопрос(2)
Образец таблицы и данные
create table Product(Location char(2), Date_time datetime, Item_sold varchar(20))
insert Product select 'VA', '20101210 1:30:00 PM' ,'Candy'
insert Product select 'VA', '20101210 3:30:00 PM' ,'Chips'
insert Product select 'VA', '20101213 12:50:00 AM' ,'Wine'
insert Product select 'DC', '20101213 8:00:00 AM' ,'Gum'
insert Product select 'DC', '20101213 12:30:00 PM' ,'Bags'
insert Product select 'DC', '20101213 1:16:00 PM' ,'Cheese'
insert Product select 'DC', '20101213 12:00:00 AM' ,'Hotdog'
insert Product select 'NJ', '20101215 12:00:00 AM' ,'Coffee'
insert Product select 'NJ', '20101215 1:15:00 PM' ,'Beers'
insert Product select 'NJ', '20101215 3:45:00 AM' ,'Cream'
T-SQL для получения необходимых результатов
declare @start datetime
declare @end datetime
select @start = '20101208', @end = '20110105'
declare @sql nvarchar(max);
-- generate the column names
select @sql = coalesce(@sql + ',', '') + QuoteName(Convert(char(5),@start,101)+' - '+Convert(char(5),DT,101))
from (
select @start + number DT
from master..spt_values
where type='P' and number between 0 and DATEDIFF(D,@start,@end)) T;
-- replace the column names into the generic PIVOT form
set @sql = REPLACE('
;with COUNTS AS (
select p.location, Convert(char(5),@start,101)+'' - ''+Convert(char(5),@start + v.number,101) DT, X.C
from
(
Select distinct location From Product
where Date_time >= @start and Date_time < @end+1 -- * the date after, to handle the times
) p
inner join master..spt_values v on v.type=''P'' and v.number between 0 and DATEDIFF(D,@start,@end)
cross apply
(
select COUNT(*) C from product p2
where p2.Location=p.Location
and p2.date_time >= @start and p2.date_time < @start + v.number +1
) X
)
select location, :columns:
from COUNTS p
pivot (max(C) for DT in (:columns:)) pv',
':columns:', @sql)
-- execute for the results
exec sp_executesql @sql, N'@start datetime,@end datetime', @start, @end
Я только что выполнил скрипт и произошла ошибка пары. Вот ошибка скрипта: Неверный синтаксис рядом с ключевым словом «внутренний». Неверный синтаксис рядом с 'X'.
Это версия MS SQL Server 2000/5/8. Спасибо за ваш другой совет.
@joe - я просто скопировал и вставил оба блока в ssms как есть и запустил его в tempdb, и он дал результаты без ошибок. Это то, что вы сделали? Можете ли вы подтвердить, что версия SQL Server> = 2005?
Спасибо, тестирую ваш скрипт сейчас. Я также обновил вывод, чтобы сделать его более понятным. Один вопрос для вас, предположим, что я создаю временную таблицу с большим набором данных (диапазон дат от 10/1/09 до 10/1/10), будет ли работать ваш сценарий?
от Cyberkiwi, слишком сложно. Вам нужно сделать то, что называется «вращение стола». Предполагая, что у вас есть таблица продуктов, как это:
create table product
(
location char(2) not null ,
date_time datetime not null ,
item_sold varchar(32) not null ,
primary key clustered ( location , date_time ) ,
)
SQL, который вы хотите, является следующим оператором SELECT:
select Location = loc.location ,
"12/8" = t8.sales_cnt ,
"12/8 - 12/09" = t9.sales_cnt ,
"12/8 - 12/10" = t10.sales_cnt ,
"12/8 - 12/11" = t11.sales_cnt ,
"12/8 - 12/12" = t12.sales_cnt ,
"12/8 - 12/13" = t13.sales_cnt
from ( select distinct location from #product ) loc
left join ( select location , sales_cnt = count(*) from #product where date_time between '2011-12-08 00:00:00.000' and '2011-12-08 23:59:59.997' group by location ) t8 on t8.location = loc.location
left join ( select location , sales_cnt = count(*) from #product where date_time between '2011-12-08 00:00:00.000' and '2011-12-09 23:59:59.997' group by location ) t9 on t9.location = loc.location
left join ( select location , sales_cnt = count(*) from #product where date_time between '2011-12-08 00:00:00.000' and '2011-12-10 23:59:59.997' group by location ) t10 on t10.location = loc.location
left join ( select location , sales_cnt = count(*) from #product where date_time between '2011-12-08 00:00:00.000' and '2011-12-11 23:59:59.997' group by location ) t11 on t11.location = loc.location
left join ( select location , sales_cnt = count(*) from #product where date_time between '2011-12-08 00:00:00.000' and '2011-12-12 23:59:59.997' group by location ) t12 on t12.location = loc.location
left join ( select location , sales_cnt = count(*) from #product where date_time between '2011-12-08 00:00:00.000' and '2011-12-13 23:59:59.997' group by location ) t13 on t13.location = loc.location
order by 1
и, не усложняя, как ваш код будет масштабироваться для переменного диапазона дат? Мне всегда не нравился метод BETWEEN + «xxx 23: 59: 59.997», тем более что теперь вы можете получать даты ближе к полуночи в SQL Server 2008. Меньше, чем следующий день, кажется, работает лучше всего и точно представляет условие.
Благодарю. это работает для приведенных выше примеров данных, но как бы вы поступили, когда у вас есть больший набор данных?