предложена программа. В этой программе отсутствует предварительная настройка названия полей накопления , поэтому конечный SQL-код получается излишне длинным.
В предлагаемом коде шахматка формируется последовательно с нарастанием по каждой группе колонок с помощью оператора соединения FULL JOIN и функции NVL.
SELE 0
CREATE CURSOR CRAS (ID_TOV I(4),NMAT C(30),ED_IZM C(4),CENA N(18,2),ID_RAS I(4),NAMR C(30),KOL N(18,2),SUMA N(18,2))
INSERT INTO CRAS VALUES (1343,'Болт м10*30.58.019/7798 ','КГ ',2.41,14953,'Сеялка спу-6д-01 ',0.19,0.45)
INSERT INTO CRAS VALUES (1343,'Болт м10*30.58.019/7798 ','КГ ',2.41,14954,'Сеялка спу-6л-01 ',0.06,0.15)
INSERT INTO CRAS VALUES (1352,'Болт м6*16.58.019/7798 ','КГ ',2.37,14936,'Сеялка ств-12 ',0.90,2.13)
INSERT INTO CRAS VALUES (494,'Велопропилен ','КГ ',6.42,14937,'Сеялка спу-4д ',0.18,1.12)
INSERT INTO CRAS VALUES (494,'Велопропилен ','КГ ',6.42,14953,'Сеялка спу-6д-01 ',0.15,0.96)
INSERT INTO CRAS VALUES (494,'Велопропилен ','КГ ',6.42,14954,'Сеялка спу-6л-01 ',0.05,0.32)
INSERT INTO CRAS VALUES (1153,'Гайка м12.8.016 гост 5915 ','КГ ',2.90,14937,'Сеялка спу-4д ',0.67,1.95)
INSERT INTO CRAS VALUES (1417,'Гайка м12.8.016 гост 5915 ','КГ ',3.51,14953,'Сеялка спу-6д-01 ',0.48,1.68)
INSERT INTO CRAS VALUES (1417,'Гайка м12.8.016 гост 5915 ','КГ ',3.51,14954,'Сеялка спу-6л-01 ',0.18,0.62)
INSERT INTO CRAS VALUES (1153,'Гайка м12.8.016 гост 5915 ','КГ ',2.90,14936,'Сеялка ств-12 ',0.70,2.04)
INSERT INTO CRAS VALUES (1417,'Гайка м12.8.016 гост 5915 ','КГ ',3.51,14936,'Сеялка ств-12 ',0.90,3.15)
INSERT INTO CRAS VALUES (993,'Гайка м4.5.016 гост 5915 ','КГ ',0.53,14937,'Сеялка спу-4д ',0.01,0.00)
INSERT INTO CRAS VALUES (993,'Гайка м4.5.016 гост 5915 ','КГ ',0.53,14953,'Сеялка спу-6д-01 ',0.01,0.00)
INSERT INTO CRAS VALUES (993,'Гайка м4.5.016 гост 5915 ','КГ ',0.53,14954,'Сеялка спу-6л-01 ',0.00,0.00)
INSERT INTO CRAS VALUES (1134,'Гайка м6.5.016 гост 5915 ','КГ ',2.20,14937,'Сеялка спу-4д ',0.14,0.31)
INSERT INTO CRAS VALUES (1134,'Гайка м6.5.016 гост 5915 ','КГ ',2.20,14953,'Сеялка спу-6д-01 ',0.09,0.20)
INSERT INTO CRAS VALUES (1134,'Гайка м6.5.016 гост 5915 ','КГ ',2.20,14954,'Сеялка спу-6л-01 ',0.03,0.07)
INSERT INTO CRAS VALUES (1134,'Гайка м6.5.016 гост 5915 ','КГ ',2.20,14936,'Сеялка ств-12 ',0.15,0.33)
INSERT INTO CRAS VALUES (1134,'Гайка м6.8.016 гост 5915 ','КГ ',2.20,14936,'Сеялка ств-12 ',0.14,0.32)
INSERT INTO CRAS VALUES (1364,'Гайка м8.5.016 гост 5915 ','КГ ',2.90,14937,'Сеялка спу-4д ',0.04,0.12)
INSERT INTO CRAS VALUES (1364,'Гайка м8.5.016 гост 5915 ','КГ ',2.90,14953,'Сеялка спу-6д-01 ',0.04,0.10)
INSERT INTO CRAS VALUES (1364,'Гайка м8.5.016 гост 5915 ','КГ ',2.90,14954,'Сеялка спу-6л-01 ',0.01,0.03)
INSERT INTO CRAS VALUES (1364,'Гайка м8.5.016 гост 5915 ','КГ ',2.90,14936,'Сеялка ств-12 ',0.03,0.09)
INSERT INTO CRAS VALUES (36630,'Гродномид па6-л-св30 ','КГ ',3.28,14937,'Сеялка спу-4д ',5.29,17.33)
INSERT INTO CRAS VALUES (36630,'Гродномид па6-л-св30 ','КГ ',3.28,14953,'Сеялка спу-6д-01 ',2.88,9.45)
INSERT INTO CRAS VALUES (36630,'Гродномид па6-л-св30 ','КГ ',3.28,14954,'Сеялка спу-6л-01 ',0.96,3.15)
INSERT INTO CRAS VALUES (36630,'Гродномид па6-л-св30 ','КГ ',3.28,14936,'Сеялка ств-12 ',47.75,156.62)
INSERT INTO CRAS VALUES (36630,'Гродномид па6-лта-св5 ','КГ ',3.28,14937,'Сеялка спу-4д ',2.69,8.82)
INSERT INTO CRAS VALUES (36630,'Гродномид па6-лта-св5 ','КГ ',3.28,14954,'Сеялка спу-6л-01 ',0.21,0.69)
INSERT INTO CRAS VALUES (36630,'Гродномид па6-лта-св5 ','КГ ',3.28,14936,'Сеялка ств-12 ',0.06,0.20)
INSERT INTO CRAS VALUES (36750,'Леска 0.4мм ','М ',19.50,14937,'Сеялка спу-4д ',0.02,0.44)
INSERT INTO CRAS VALUES (36750,'Леска 0.4мм ','М ',19.50,14953,'Сеялка спу-6д-01 ',0.02,0.37)
INSERT INTO CRAS VALUES (36750,'Леска 0.4мм ','М ',19.50,14954,'Сеялка спу-6л-01 ',0.01,0.12)
INSERT INTO CRAS VALUES (31851,'Пластикат "BAYBLEND" ','КГ ',7.09,14936,'Сеялка ств-12 ',5.04,35.73)
INSERT INTO CRAS VALUES (31851,'Пластикат авс-2020-30 (черный)','КГ ',7.09,14937,'Сеялка спу-4д ',0.84,5.96)
INSERT INTO CRAS VALUES (31851,'Пластикат авс-2020-30 (черный)','КГ ',7.09,14953,'Сеялка спу-6д-01 ',0.36,2.55)
INSERT INTO CRAS VALUES (31851,'Пластикат авс-2020-30 (черный)','КГ ',7.09,14954,'Сеялка спу-6л-01 ',0.12,0.85)
INSERT INTO CRAS VALUES (951,'Пластикат пвх ш-62 ','КГ ',2.71,14937,'Сеялка спу-4д ',105.67,286.78)
INSERT INTO CRAS VALUES (951,'Пластикат пвх ш-62 ','КГ ',2.71,14953,'Сеялка спу-6д-01 ',70.20,190.52)
INSERT INTO CRAS VALUES (951,'Пластикат пвх ш-62 ','КГ ',2.71,14954,'Сеялка спу-6л-01 ',23.40,63.51)
INSERT INTO CRAS VALUES (951,'Пластикат пвх ш-62 ','КГ ',2.71,14936,'Сеялка ств-12 ',29.20,79.24)
INSERT INTO CRAS VALUES (952,'Пластикат пвх ув-11/3 ','КГ ',2.71,14937,'Сеялка спу-4д ',68.46,185.79)
INSERT INTO CRAS VALUES (952,'Пластикат пвх ув-11/3 ','КГ ',2.71,14953,'Сеялка спу-6д-01 ',46.13,125.18)
INSERT INTO CRAS VALUES (952,'Пластикат пвх ув-11/3 ','КГ ',2.71,14954,'Сеялка спу-6л-01 ',15.38,41.73)
INSERT INTO CRAS VALUES (952,'Пластикат пвх ув-11/3 ','КГ ',2.71,14936,'Сеялка ств-12 ',22.55,61.20)
INSERT INTO CRAS VALUES (31851,'Пластикат700-314абс/серый ','КГ ',7.09,14937,'Сеялка спу-4д ',50.15,355.56)
INSERT INTO CRAS VALUES (31851,'Пластикат700-314абс/серый ','КГ ',7.09,14953,'Сеялка спу-6д-01 ',33.48,237.35)
INSERT INTO CRAS VALUES (31851,'Пластикат700-314абс/серый ','КГ ',7.09,14954,'Сеялка спу-6л-01 ',11.16,79.12)
INSERT INTO CRAS VALUES (36630,'Полиамид па6-210/310 ','КГ ',3.28,14937,'Сеялка спу-4д ',24.96,81.87)
INSERT INTO CRAS VALUES (36630,'Полиамид па6-210/310 ','КГ ',3.28,14953,'Сеялка спу-6д-01 ',19.13,62.74)
INSERT INTO CRAS VALUES (36630,'Полиамид па6-210/310 ','КГ ',3.28,14954,'Сеялка спу-6л-01 ',6.43,21.08)
INSERT INTO CRAS VALUES (36630,'Полиамид па6-210/310 ','КГ ',3.28,14936,'Сеялка ств-12 ',23.96,78.59)
INSERT INTO CRAS VALUES (295,'Полистирол псм-115 ','КГ ',0.40,14936,'Сеялка ств-12 ',2.70,1.08)
INSERT INTO CRAS VALUES (36708,'Полиэт. пэвд 15803-20 ','КГ ',2.08,14937,'Сеялка спу-4д ',0.50,1.04)
INSERT INTO CRAS VALUES (36708,'Полиэт. пэвд 15803-20 ','КГ ',2.08,14953,'Сеялка спу-6д-01 ',0.33,0.69)
INSERT INTO CRAS VALUES (36708,'Полиэт. пэвд 15803-20 ','КГ ',2.08,14954,'Сеялка спу-6л-01 ',0.11,0.23)
INSERT INTO CRAS VALUES (36708,'Полиэт. пэвд 15803-20 ','КГ ',2.08,14936,'Сеялка ств-12 ',0.74,1.55)
INSERT INTO CRAS VALUES (931,'Полиэт.желт.пэвд 15803-20 ','КГ ',4.33,14936,'Сеялка ств-12 ',0.01,0.04)
INSERT INTO CRAS VALUES (36708,'Полиэт.желт.пэвд 15803-20 ','КГ ',2.08,14936,'Сеялка ств-12 ',0.84,1.75)
INSERT INTO CRAS VALUES (36707,'Полиэтилен 277-73 ','КГ ',2.79,14937,'Сеялка спу-4д ',0.71,1.99)
INSERT INTO CRAS VALUES (36707,'Полиэтилен 277-73 ','КГ ',2.79,14953,'Сеялка спу-6д-01 ',0.32,0.90)
INSERT INTO CRAS VALUES (36707,'Полиэтилен 277-73 ','КГ ',2.79,14954,'Сеялка спу-6л-01 ',0.11,0.30)
INSERT INTO CRAS VALUES (36707,'Полиэтилен 277-73 ','КГ ',2.79,14936,'Сеялка ств-12 ',0.57,1.59)
INSERT INTO CRAS VALUES (30477,'Резина 7-ирп-1347 ','КГ ',5.59,14937,'Сеялка спу-4д ',3.36,18.78)
INSERT INTO CRAS VALUES (30477,'Резина 7-ирп-1347 ','КГ ',5.59,14953,'Сеялка спу-6д-01 ',0.30,1.68)
INSERT INTO CRAS VALUES (30477,'Резина 7-ирп-1347 ','КГ ',5.59,14954,'Сеялка спу-6л-01 ',0.10,0.56)
INSERT INTO CRAS VALUES (36742,'Резиновая смесь 7-B-14 ','КГ ',4.96,14937,'Сеялка спу-4д ',0.09,0.45)
INSERT INTO CRAS VALUES (36742,'Резиновая смесь 7-B-14 ','КГ ',4.96,14953,'Сеялка спу-6д-01 ',0.04,0.19)
INSERT INTO CRAS VALUES (36742,'Резиновая смесь 7-B-14 ','КГ ',4.96,14954,'Сеялка спу-6л-01 ',0.01,0.06)
INSERT INTO CRAS VALUES (36742,'Резиновая смесь 7-B-14 ','КГ ',4.96,14936,'Сеялка ств-12 ',2.24,11.11)
INSERT INTO CRAS VALUES (30477,'Смесь резин. Vб-22-14-781 ','КГ ',5.59,14937,'Сеялка спу-4д ',8.15,45.58)
INSERT INTO CRAS VALUES (30477,'Смесь резин. Vб-22-14-781 ','КГ ',5.59,14953,'Сеялка спу-6д-01 ',5.24,29.30)
INSERT INTO CRAS VALUES (30477,'Смесь резин. Vб-22-14-781 ','КГ ',5.59,14954,'Сеялка спу-6л-01 ',1.75,9.77)
INSERT INTO CRAS VALUES (30225,'Смесь резиновая я-7317(ф)-VII-','КГ ',5.45,14937,'Сеялка спу-4д ',2.78,15.15)
INSERT INTO CRAS VALUES (30225,'Смесь резиновая я-7317(ф)-VII-','КГ ',5.45,14953,'Сеялка спу-6д-01 ',6.18,33.69)
INSERT INTO CRAS VALUES (30225,'Смесь резиновая я-7317(ф)-VII-','КГ ',5.45,14954,'Сеялка спу-6л-01 ',2.06,11.23)
INSERT INTO CRAS VALUES (30225,'Смесь резиновая я-7317(ф)-VII-','КГ ',5.45,14936,'Сеялка ств-12 ',15.83,86.27)
INSERT INTO CRAS VALUES (931,'Супер концент.2600 желтый ','КГ ',4.33,14936,'Сеялка ств-12 ',0.01,0.04)
INSERT INTO CRAS VALUES (655,'Суперконцентрат скгп-501 синий','КГ ',11.88,14937,'Сеялка спу-4д ',0.79,9.40)
INSERT INTO CRAS VALUES (655,'Суперконцентрат скгп-501 синий','КГ ',11.88,14953,'Сеялка спу-6д-01 ',0.77,9.11)
INSERT INTO CRAS VALUES (655,'Суперконцентрат скгп-501 синий','КГ ',11.88,14954,'Сеялка спу-6л-01 ',0.26,3.04)
INSERT INTO CRAS VALUES (655,'Суперконцентрат скгп-501 синий','КГ ',11.88,14936,'Сеялка ств-12 ',0.26,3.09)
INSERT INTO CRAS VALUES (930,'Суперконц.черный MAXITEN ','КГ ',1.92,14936,'Сеялка ств-12 ',0.30,0.58)
nCount=AFIELDS(ASTR)
* (ID_TOV I(4),NMAT C(30),ED_IZM C(4),CENA N(18,2),ID_RAS I(4),NAMR C(30),KOL N(18,2),SUMA N(18,2))
FOR I=1 TO nCount
ASTR(I,5)=0
ENDFOR
* ПОМЕТИМ ПОЛЯ КОТОРЫЕ БУДУТ ГРУППИРОВАТЬ ПО СТРОКАМ
ASTR(1,5)=1
ASTR(2,5)=1
ASTR(3,5)=1
ASTR(4,5)=1
* ПОМЕТИМ ПОЛЯ КОТОРЫЕ БУДУТ ГРУППИРОВАТЬ ПО СТОЛБЦАМ
ASTR(5,5)=2
ASTR(6,5)=2
* ПОМЕТИМ СУММИРУЕМЫЕ ПОЛЯ
ASTR(7,5)=3
ASTR(8,5)=3
*ВЫРАЖЕНИЕ ДЛЯ ИНДЕКСА ПОЛЕЙ НАКОПЛЕНИЯ Т.К. ИМЯ ПОЛЯ НАКОПЛЕНИЯ БУДЕМ ЗАДАВАТЬ В ФОРМЕ:СУМИРУЕМОЕ ПОЛЕ_ИНДЕКС
IND="TRANSFORM(ID_RAS)"
* IND="NAMR"
DO SAXMGEN1 WITH ASTR,IND
BROW
PROCEDURE SAXMGEN1
PARAMETERS ASTRU,INDS
LOCAL nCount,cTableAlias,lcSQL,TYP,DL,KD,ALCID,CS,CR,badchars
badchars = '/,-=:;!@#$%&*.<>()?[]\'+'+'+CHR(34)+CHR(39)+" " && ВЗЯТО ИЗ _GENXTAB
nCount=ALEN(ASTRU,1)
cTableAlias=ALIAS()
* СПМСОК ПОЛЕЙ КОТОРЫЕ БУДУТ ГРУППИРОВАТЬ ПО СТРОКАМ
lRow=''
FOR I = 1 TO nCount
IF ASTRU(I,5)=1
lRow = lRow + " "+RTRIM(ASTRU(I,1))
ENDIF
ENDFOR
lRow=CHRTRAN(LTRIM(lRow),' ',',')
* СПМСОК ПОЛЕЙ КОТОРЫЕ БУДУТ ГРУППИРОВАТЬ ПО КОЛОНКАМ
lCol=''
FOR I = 1 TO nCount
IF ASTRU(I,5)=2
lCol = lCol + " "+RTRIM(ASTRU(I,1))
ENDIF
ENDFOR
lCol=CHRTRAN(LTRIM(lCol),' ',',')
* СПМСОК ПОЛЕЙ КОТОРЫЕ БУДУТ СУММИРОВАТЬСЯ
lSum=''
FOR I = 1 TO nCount
IF ASTRU(I,5)=3
lSum = lSum + " "+RTRIM(ASTRU(I,1))
ENDIF
ENDFOR
lSum=CHRTRAN(LTRIM(lSum),' ',',')
*СТОЛБЦЫ НАКОПЛЕНИЯ БУДУЩЕЙ ШАХМАТКИ ПОЛУЧИМ ОТСЮДА
ALCID=SYS(2015)
lcSQL = 'SELECT DISTINCT ' + lCol + ' FROM ' + cTableAlias + ' ORDER BY ' + lCol + ' INTO CURSOR ' + ALCID
=EXECSCRIPT(lcSQL)
* ОПРЕДЕЛИМ ИНДЕКС ДЛЯ ПОМЕТКИ ПОЛЕЙ НАКОПЛЕНИЯ
lcSQL = 'SELECT *,' + INDS + ' IND FROM ' + ALCID+ ' INTO CURSOR ' + ALCID + ' READWRITE '
=EXECSCRIPT(lcSQL)
*ЗАМЕНИМ СИМВОЛЫ КОТОРЫЕ НЕ ДОЛЖНЫ БЫТЬ В НАЗВАНИИ ПОЛЕЙ НА '_'
SELECT (ALCID)
GO TOP
REPLACE ALL IND WITH CHRTRANC(RTRIM(IND),M.badchars,REPLICATE("_",LEN(M.badchars)-1))
*СДЕЛАЕМ ЗАЧАТИЕ ШАХМАТКИ
CS=SYS(2015)
lcSQL = 'SELECT ' + lRow + ' FROM ' + cTableAlias + ' WHERE .F. INTO CURSOR ' + CS
=EXECSCRIPT(lcSQL)
*К КУРСОРУ CS БУДЕМ ПРИСОЕДИНЯТЬ СТОЛБЦЫ НАКОПЛЕНИЯ ИСХОДЯ ИЗ ТАБЛИЦЫ ALCID
CR=SYS(2015)
nSUM=GETWORDCOUNT(lSum,',')
sSUM=''
FOR I=1 TO nSUM
sSUM = sSUM + ',SUM('+GETWORDNUM(lSum,I,',')+') ' + GETWORDNUM(lSum,I,',')+'*'
ENDFOR
nRow=GETWORDCOUNT(lRow,',')
sNvl=''
FOR i=1 TO nRow
sNvl = sNvl +IIF(I=1,'',',') + 'NVL('+CS+'.'+GETWORDNUM(lRow,I,',')+','+CR+'.'+GETWORDNUM(lRow,I,',')+')'+' '+GETWORDNUM(lRow,I,',')
ENDFOR
SELECT (ALCID)
GO TOP
SCAN
sSUM1=sSUM
FOR I=1 TO nSUM
sSUM1=STRTRAN(sSUM1,'*','_'+IND)
ENDFOR
sWHERE=''
nCOL=GETWORDCOUNT(lCol,',')
FOR I=1 TO nCOL
sWHERE = sWHERE +IIF(I=1,'',' AND ')+GETWORDNUM(lCol,I,',') + '=' + ALCID+'.'+FIELD(I,ALCID)
ENDFOR
lcSQL = 'SELECT ' + lRow + sSUM1 + ' FROM ' + cTableAlias + ' WHERE '+ sWHERE + ' GROUP BY '+ lRow +;
' INTO CURSOR '+CR
=EXECSCRIPT(lcSQL)
sField = sNvl
IF FCOUNT(CS) > nRow
FOR I = nRow+1 TO FCOUNT(CS)
sField = sField +','+'NVL('+CS+'.'+FIELD(I,CS)+',0)' + ' '+ FIELD(I,CS)
ENDFOR
ENDIF
FOR I = nRow+1 TO FCOUNT(CR)
sField = sField +','+'NVL('+CR+'.'+FIELD(I,CR)+',0)' + ' '+ FIELD(I,CR)
ENDFOR
sOn = ''
FOR I=1 TO nRow
sOn = sOn + IIF(I=1,'',' AND ')+CR+'.'+GETWORDNUM(lRow,I,',') + '=' + CS+'.'+GETWORDNUM(lRow,I,',')
ENDFOR
lcSQL='SELECT '+ sField + ' FROM ' + CS + ' FULL JOIN '+ CR + ' ON ' + sOn + ' INTO CURSOR ' + CS
=EXECSCRIPT(lcSQL)
ENDSCAN
* CОЗДАДИМ ИТОГОВЫЕ СТОЛБЦЫ
sTotal=''
nSum=GETWORDCOUNT(lSum,',')
FOR J = 1 TO nSum
FSUM=GETWORDNUM(lSum,J,',')
sTotal=sTotal+','
FOR I = nRow+1 TO FCOUNT(CS)
IF ATC(FSUM,FIELD(I,CS))=1
sTotal = sTotal +IIF(I=1,'','+')+FIELD(I,CS)
ENDIF
ENDFOR
sTotal = sTotal + ' '+FSUM+'_TOTAL'
ENDFOR
lcSQL='SELECT *' + sTotal + ' FROM '+ CS + ' ORDER BY ' + lRow + ' INTO CURSOR ' + CS
=EXECSCRIPT(lcSQL)
* CОЗДАДИМ ИТОГОВУЮ СТРОКУ
SELECT (CS)
KP=AFIELDS(ACS)
sKLUC=''
FOR I=1 TO nROW
TYP= ACS(I,2)
DL = ACS(I,3)
KD = ACS(I,4)
sKLUC= sKLUC +IIF(I=1,'',',')+ICASE(TYP = 'C','['+REPLICATE(CHR(255),DL)+']',TYP = 'D','{^9999-12-31}',TYP $ 'NFYB',REPLICATE('9',DL-KD-2)+'.'+REPLICATE('9',KD),TYP = 'I',REPLICATE('9',9))
ENDFOR
sTotRW=''
FOR I = nRow+1 TO FCOUNT(CS)
sTotRW = sTotRW +','+'SUM('+FIELD(I,CS)+')'
ENDFOR
lcSQL='SELECT * FROM ' + CS + ' UNION ALL ' + ' SELECT ' + sKLUC+ sTotRW +' FROM ' + CS + ' INTO CURSOR SAXM'
=EXECSCRIPT(lcSQL)
USE IN (ALCID)
USE IN (CS)
USE IN (CR)
SELECT SAXM
RETURN
