10.11.2017 14:20 | |
xlvetalПосетитель Количество сообщений 124 Зарегистрирован: 14-11-2015, 14:23:25 |
Пользовательский запрос на экспорт в excel
Что надо добавить в этот код (и куда его вставить) - DECLARE @TBL_PROPS TABLE (G_ID int, PROPS varchar(max)); DECLARE @G_ID int, @P_NAME varchar(200), @PG_NAME varchar(200), @GP_VALUE varchar(max); DECLARE @PROPS nvarchar(max); DECLARE cGoods CURSOR FOR SELECT G_ID FROM TBL_GOODS; OPEN cGoods; FETCH NEXT FROM cGoods INTO @G_ID; WHILE (@@FETCH_STATUS <> -1) BEGIN SET @PROPS = ''; DECLARE cProps CURSOR FOR SELECT DISTINCT ISNULL(GP_VALUE, ''), P_NAME, ISNULL(PG_NAME, '') FROM TBL_GOODS_PROPS INNER JOIN TBL_PROPS ON GP_P_ID = P_ID LEFT OUTER JOIN TBL_PROPS_GROUPS ON P_PG_ID = PG_ID WHERE GP_G_ID = @G_ID; OPEN cProps; FETCH NEXT FROM cProps INTO @GP_VALUE, @P_NAME, @PG_NAME; WHILE (@@FETCH_STATUS <> -1) BEGIN IF @GP_VALUE <> '' BEGIN IF @PG_NAME <> '' SET @PROPS = @PROPS + 'list/' + @PG_NAME + ';'; SET @PROPS = @PROPS + @P_NAME + ':'; SET @PROPS = @PROPS + @GP_VALUE + '|'; END; FETCH NEXT FROM cProps INTO @GP_VALUE, @P_NAME, @PG_NAME; END CLOSE cProps; DEALLOCATE cProps; INSERT INTO @TBL_PROPS(G_ID, PROPS) VALUES (@G_ID, @PROPS); FETCH NEXT FROM cGoods INTO @G_ID; END; CLOSE cGoods; DEALLOCATE cGoods; SELECT g.G_ID as product_id, g.G_SID as ean, g.G_NAME as name, g.G_DESC as short_description, g.G_DESC_FULL as description, g.G_REST as qty, CASE WHEN g.G_PRESENT > 0 THEN 'äà' ELSE 'íåò' END as unlimited, CASE WHEN g.G_ACTIVE > 0 THEN 'äà' ELSE 'íåò' END as publish, REPLACE(gr.GR_FULL_NAME, '\', '/') as category, REPLACE(LTRIM(STR(g.price, 10, 2)), '.', ','), (SELECT B_NAME FROM TBL_BRANDS b WHERE b.B_ID = g.G_B_ID) as manufacturer, CASE WHEN g.G_ACTION > 0 THEN 'äà' ELSE 'íåò' END as label, p.PROPS as extra_fields FROM TBL_GOODS g, TBL_GROUPS gr, @TBL_PROPS p WHERE g.G_GR_ID = gr.GR_ID and g.G_ID = p.G_ID ; Что бы выгружало еще и столбец тип товара |