#sql #sql-server
#sql #sql-server
Вопрос:
Я все еще изучаю SQL (самоучка) и новичок, поэтому прошу прощения, если мой код выглядит некорректно.
Я пытаюсь настроить динамическую сводную таблицу, которая выполняет следующее:
- Динамически извлекает предложения из cataloginfo и вставляет страницы (wpfrom)
- Снова динамически захватывает предложения, на этот раз захватывает catordpolicy из таблицы pic704current
Если я удалю любую строку, она будет работать, но обе строки выдают ошибку, и я не уверен в синтаксисе или в том, как правильно заставить это работать. Я немного проверил, что сделали другие, но не нашел ничего, что работает. Я предполагаю, что должен быть простой способ включить это, но я недостаточно осведомлен, чтобы разобраться в этом на данный момент.
Ниже приведен весь код. Любая помощь или толчок в правильном направлении были бы высоко оценены!!
DECLARE @cols1 AS NVARCHAR(MAX)='';
DECLARE @cols2 AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';
SELECT @cols1 = @cols1 stuff((select ',"' offer '"'
FROM catcov y join CatalogInfo z on (y.MailYear = z.mailyear) and (y.offer = z.catalog)
WHERE z.brand = 'Seventh Avenue'
and season_id = 'F20'
and offer_type In('catalog', 'insert', 'kicker', 'statement insert', 'bangtail', 'onsert', 'outside ad')
ORDER BY ActiveDate, Offer for xml path('')),1,1,'')
SELECT @cols2 = @cols2 stuff((select ',"' offer '"'
FROM catcov y join CatalogInfo z on (y.MailYear = z.mailyear) and (y.offer = z.catalog)
WHERE z.brand = 'Seventh Avenue'
and season_id = 'F20'
and offer_type In('catalog', 'insert', 'kicker', 'statement insert', 'bangtail', 'onsert', 'outside ad')
ORDER BY ActiveDate, Offer for xml path('')),1,1,'')
set @query =
'SELECT * FROM
(SELECT DISTINCT
CONVERT(INT, a.article) as PackNum
,a.ARTICLENAME
,CONVERT(INT, a.RD) as RD
,CONVERT(INT, a.SFC) as SFC
,a.ITEMMERCH as Merch
,Case when a.ARTICLE = q.PackNum then ''No'' else ''Yes'' End as Unbuilt
,RIGHT(''00'' wpfrom,3) as wpfrom
,a.EBD as Excl
,a.PREVIOUS as Previous
,N.catordpolicy
,b.offer as offer1
,b.offer as offer2
FROM Lago_CID_Master a join catcov b on (a.offeryear = b.mailyear) and (a.offer = b.offer)
JOIN cataloginfo c on (b.offer = c.catalog) and (b.mailyear = c.mailyear)
JOIN pic704current N on (b.offer = N.CatID) and (b.mailyear = N.Year)
LEFT JOIN
(SELECT packnum, catid, Page, Year, catordpolicy
FROM PIC704Current e JOIN CatalogInfo f ON (e.CatID = f.Catalog) AND (e.Year = f.MailYear)
WHERE LEFT(f.Brand,5) = ''Seven''
AND f.SeasonID = ''F20''
AND (f.mediaid in(''CAT Catalog'',''SCAT Sale Catalog'',''SSTF Sale Statement Stuff'',''STUF Statement Stuffer'',''PKG Package Insert'',''SPKG Sale Pkg Insert'')
)) q
ON a.ARTICLE = q.PackNum
WHERE LEFT(c.brand, 5) = ''Seven''
AND season_id = ''F20'' and offer_type In(''catalog'', ''insert'', ''kicker'', ''statement insert'', ''bangtail'', ''onsert'', ''outside ad'')) src
pivot
(Min(wpfrom) for Offer1 in (' @cols1 ')) as p1
pivot
(Max(catordpolicy) for offer2 in (' @cols2 ')) as p2'
execute(@query)
Это то, что я получаю в настоящее время:
Это то, что я ищу, чтобы получить:
Обновление 2: текст данных перед сводной точкой
Packnum Articlename RD SFC Merch Unbuilt Wpfrom ordpolicy offer
600209 23oz. HOL COOKIES 9 29 FF No 160 EQ
600209 23oz. HOL COOKIES 9 29 FF No 160 N EQ
600265 36 Merry Christmas PF 5 6 FF No 042 DJ
600265 36 Merry Christmas PF 5 6 FF No 042 N DJ
Обновление 3:
Я добился некоторого прогресса и обновил код до того, что у меня есть сейчас. При попытке запустить этот код я получаю следующую ошибку:
Msg 265, Level 16, State 1, Line 37
The column name "DK " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "EZ " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "DD " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "DQ " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "EL " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "DH " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "EH " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "DC " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "DN " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "EG " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "EP " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "EQ " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 37
The column name "DJ " specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 37
The column 'DK ' was specified multiple times for 'p2'.
Я получаю эту ошибку для каждого «Предложения» в моих данных. Я чувствую, что близок к разрешению, но я не уверен, чего мне не хватает.
Комментарии:
1. Динамические сводные точки — это не начальный уровень, а зачастую и не промежуточный. Они могут быть довольно сложными. Здесь вам будет лучше показать свои образцы данных и ожидаемые результаты. Кроме того, пожалуйста, используйте соответствующие псевдонимы для ваших объектов.
catcov
в нем нет ни однойb
, но у него есть псевдонимb
. ДляCatalogInfo
да, у него естьf
в is, но это действительно неправильный выбор. Вредные привычки, от которых нужно отказаться: использование псевдонимов таблиц, таких как (a, b, c) или (t1, t2, t3)2. @Larnu Привет, спасибо за ответ. Как я уже сказал, я самоучка, что означает, что я не уверен в своем программировании как человек, прошедший формальное обучение. Я ценю, что вы считаете, что мой уровень квалификации выше, чем я думаю. По вашей просьбе я добавил изображение того, что я получаю, и того, что я хотел бы получить из своего кода. Еще раз спасибо!
3. Нам нужно увидеть, как выглядят ваши данные до вашей попытки, а не после. Не забудьте потратить время на предоставление его в виде
text
, а не в виде изображения.4. Хорошо, я добавил данные до того, как они будут развернуты.
Ответ №1:
SET NoCount ON
DECLARE @cols1 AS NVARCHAR(MAX)=''; DECLARE @query1 AS NVARCHAR(MAX)='';
SELECT @cols1 = @cols1 stuff((select ',"' offer '"'
FROM catcov y join CatalogInfo z on (y.MailYear = z.mailyear) and (y.offer = z.catalog)
WHERE z.brand = 'Seventh Avenue' and season_id = 'F20' and offer_type In('catalog', 'insert', 'kicker', 'statement insert', 'bangtail', 'onsert', 'outside ad')
ORDER BY ActiveDate, Offer for xml path('')),1,1,'')
set @query1 = 'SELECT * into #temp1 FROM
(SELECT DISTINCT CONVERT(INT, a.article) as PackNumber ,a.ARTICLENAME ,CONVERT(INT, a.RD) as RD ,CONVERT(INT, a.SFC) as SFC ,a.ITEMMERCH as Merch ,Case when a.ARTICLE = q.PackNum then ''No'' else ''Yes'' End as Unbuilt ,CASE WHEN Cast(a.wpfrom as int) >=7000 then ''EOC'' WHEN Cast(a.wpfrom as int) between 700 and 800 or a.spreadname like ''%CHECK%'' then ''CHK'' WHEN a.spreadname like ''%ORDER FORM%'' or a.spreadname like ''%CTOB%'' then ''COF'' WHEN a.spreadname like ''%OFC%'' then ''OFC'' WHEN a.spreadname like ''%EXOBC%'' then ''WR'' WHEN a.spreadname like ''%IBC%'' then ''IB'' WHEN Cast(a.wpfrom as int) = 2 then ''IF'' WHEN a.spreadname like ''%OBC%'' then ''OB'' ELSE right(''00'' a.wpfrom,3) END AS Pages
,a.EBD as Excl ,a.PREVIOUS as Previous ,b.offer as offer
FROM Lago_CID_Master a join catcov b on (a.offeryear = b.mailyear) and (a.offer = b.offer) JOIN cataloginfo c on (b.offer = c.catalog) and (b.mailyear = c.mailyear) JOIN pic704current N on (b.offer = N.CatID) and (b.mailyear = N.Year)
LEFT JOIN
(SELECT packnum, catid, Page, Year, catordpolicy FROM PIC704Current e JOIN CatalogInfo f ON (e.CatID = f.Catalog) AND (e.Year = f.MailYear)
WHERE LEFT(f.Brand, 5) = ''Seven'' AND f.SeasonID = ''F20'' AND (f.mediaid in(''CAT Catalog'',''SCAT Sale Catalog'',''SSTF Sale Statement Stuff'',''STUF Statement Stuffer'',''PKG Package Insert'',''SPKG Sale Pkg Insert''))) q
ON a.ARTICLE = q.PackNum
WHERE LEFT(c.Brand, 5) = ''Seven'' AND season_id = ''F20'' and offer_type In(''catalog'', ''insert'', ''kicker'', ''statement insert'', ''bangtail'', ''onsert'', ''outside ad'')) src
pivot (Min(src.Pages) for Offer in (' @cols1 ')) as p1
SELECT * into #temp2 FROM
(SELECT DISTINCT CONVERT(INT, a.packnum) as Article ,b.offer as offer ,a.catordpolicy
FROM pic704current a join catcov b on (a.year = b.mailyear) and (a.catid = b.offer) JOIN cataloginfo c on (b.offer = c.catalog) and (b.mailyear = c.mailyear) Left Join
(SELECT packnum, catid, Page, Year, catordpolicy
FROM PIC704Current e JOIN CatalogInfo f ON (e.CatID = f.Catalog) AND (e.Year = f.MailYear)
WHERE LEFT(f.Brand, 5) = ''Seven'' AND f.SeasonID = ''F20'' AND (f.mediaid in(''CAT Catalog'',''SCAT Sale Catalog'',''SSTF Sale Statement Stuff'',''STUF Statement Stuffer'',''PKG Package Insert'',''SPKG Sale Pkg Insert''))) nn
ON a.packnum = nn.PackNum and a.catid = nn.catid
WHERE LEFT(c.Brand, 5) = ''Seven'' AND season_id = ''F20'' and offer_type In(''catalog'', ''insert'', ''kicker'', ''statement insert'', ''bangtail'', ''onsert'', ''outside ad'')) src2
pivot (Max(src2.catordpolicy) for offer in (' @cols1 ')) as p2
select *
from #temp1 a
left join #temp2 b
on a.PackNumber = b.Article'
execute(@query1)