Блог

У меня есть следующий запрос.. Мне нужно указать торгового представителя в виде столбцов и информацию в ОПИСАНИИ под соответствующим торговым представителем. Сводная таблица не работает, поскольку я ничего не агрегирую. Не нашел ничего, что мне нравится, поэтому я прошу вашей помощи.. Спасибо за ваше время.

 SALES_REP   DESCRIP
AG  (AXI01) EGCG EXTENDED RELEASE CAPSULES (RD)
AG  (DLI01) NWE-01 ESSENTIAL DIGESTIVES ()
AG  (KID01) KIDNEYHOOD S3 (UT)
AG  (AXI01) AB-4166 DISSOLUTION TESTING (RD)
AG  (AXI01) MCC SPHERES COATED W BLACK IRON OXIDE (RD)
AG  (ELY01) MATTER REFORMULATION (RON/A)
BD  (SMA01) VEGETARIAN PROTEIN BLEND W/GREENS  (RD)
BD  (MIT01) ARGIINE AAKG GARN AND TABLETS (ANGEL)
BD  (DLI01) BISMUTH/FURAZOLIDONE/CLARITHROMYCIN COMBINATION DRUG PRODUCT_UTECH GUM (NL)
BD  (00390) COAT 8 KILOS OF TABLET CORES WITH ABOUT 1 % COATING THAT HAS ADDED  SAFETRACES DNA MARKER   ()
BD  (EEX02) GRANULATE SOY PROTEIN WITH ACACIA  ()
BD  (LIQ01) GRANULATE SAMPLES WITH WATER ONLY , SPRAY DRIED LEMON, ORANGE , GRAPEFUIT POWDERS. BASE ALREADY CONTAINS 25-30% ACACIA GUM ()
BD  (BRA01) FOR QUOTING PURPOSES , DEVELOP FORMULA FOR DEXBROMPHENIRAMINE MALEATE 2 MG AND PHENAZOPYRIDE HCL (AZO) 100 amp; 200 MG TABS ()
CB  (NUS04) JUNGAMALS JAPAN 2021 25 BOTTLE Ramp;D PILOT FOR STABILITY TESTS (ANGEL)
CB  (NUS01) US DIGESTIVE 90 COUNT VERSION OF NUS292C (ANGEL)
CB  (LIM01) QUICK DISSOLVE TABLET - RTA SHOT (BERRY) (UT)
CB  (LIM01) QUICK DISSOLVE TABLET - THRIVE-HGH (TROPICAL) (UT)
CB  (AMB01) AMBRA/LOVIDIA XR FORMULA WITH GYMNEMA SLYVESTRE 465 MG (RD)
CB  (SHA02) RITESTART KIDS amp; TEENS REFORMULATION (UT)
CB  (BAS01) SEROVITAL B CAPSULE FORMULA OPTIONS - YELLOW PWDR IN CLEAR amp; "00" WHITE CASPULE SAMPLES (USHA)
CB  (NUS01) TR90 FIT US PLACEBO 20 KG RUN 60 CNT APPROX 600-700 UNLABELED BOTTLES (ANGEL)
MG  (AME02) POWDER BLENDING (ANGEL)
MG  (ZER01) CHRONDOITIN SULFATE COATING X2 (AG)
MG  (ELU01) TOBRAMYCIN GRANULES (NL)
MG  (BRI01) BRILLIANT HEALTH TRANSMIT GRAN AND TABLET (UT)
RND (DLI01) EFFERVESCENT PAIN PROCESS VALIDATION (RD)
RND (DLI01) GRAN D MAGNET QUALIFICATION (RD)
RND (DLI01) BLENDER CAPACITY STUDY (RD)
RND (DLI01) COATING PAN Aamp;B QUALIFICATION (RD)
RND (WYE06) SODD-006 STABILITY EVALUATION (NL)
RND (WYE06) CAL160 PROCESS VALIDATION (RD)
SH  (LIV02) FORMULATION OF HYDRATION DRINK MIX (USHA)
SH  (KOB01) ASPIRIN CAFFEINE POWDER (USHA)
SH  (COP01) APPLE CIDER VINEGAR EFFERVESCENT TABLETS (USHA)
SH  (COP01) SURVIVAL TABLETS 720CT (NATE)
SH  (USA01) GRANULATE 10 KILO POWDER BLEND - USANA (USHA)
TV  (LIF01) PETANDIM LIF-07T AND LIF-13T NEW OMEGA (ANGEL)
TV  (LIF01) L-CARNITINE ISSUE NRF1 JP (ANGEL)
TV  (SHA01) SOY-02 REPLACEMENT IN SHK-06 AND SHK-22/25 PRODUCTS (AG)
TV  (ELY01) PROBIOTIC CAPSULES ()
TV  (VYM01) VYMUNE CHEWABLE TABLETS (UT)
TV  (QUL01) IMMUNITY BOOSTER (UT)
TV  (DOT01) DOT-06 UPDATED FORMULA WITH NEW OIL, STEVIA @ 95% NEED 2 OPTIONS ()
TV  (DOT01) MITO2MAX ()
TV  (DOT01) TERRAZYME ()
TV  (LIF02) GRANULATE 2 PREBLENDED POWDER PRODUCTS , PARTYAID AND FOCUSAID . USE WATER ONLY ()
TV  (PAR01) CALCIUM CITRATE WITH PROBIOTIC STICKPACK ()
  

 SELECT        SALES_REP, '('   CUSTOMER_ID   ') '   PROJECT_DESCRIPTION   ' ('   IsNull(RD_REP,'')   ')' as DESCRIP
FROM            DLI_RND_PROJECTS
WHERE        (STATUS = N'OPEN')
ORDER BY SALES_REP
  

Итак, что-то вроде этого

 AG           BD            CB        ETC
-------------------------------------------------
AXI01...      SMA01...      NUS04...
DLI01...      MIT01...      NUS01...
KID01...      DLI01...      LIM01...
  

Хммм . , , я думаю, вы хотите:

 select max(case when sales_rep = 'AG' then col end) as ag,
       max(case when sales_rep = 'BD' then col end) as bd,
       . . . 
from (select t.*, row_number() over (partition by sales_rep, col order by col) as seqnum
      from t cross apply
           (values (substring(descr, 2, 5))) v(col)
     ) t
group by seqnum;