asked on

T-SQL problem...

Hi,

I have the following query:
SELECT DISTINCT per.perID, per.rxDate,
cnt.cntNom,
      cnt.cntOrdre,
      --med.idxList,
      CASE WHEN cntNom = 'MATIN' THEN med.medFreqAM
       WHEN cntNom = 'MIDI' THEN med.medFreqMD
       WHEN cntNom = 'SOUPER' THEN med.medFreqSP
WHEN cntNom = 'COUCHER' THEN med.medFreqHS
      END Quantite,
      medi.medNom, medi.medTemplateFile
FROM dbo.TB_disFichePatient fic
INNER JOIN dbo.TB_disRxPeriode per ON fic.ficheID = per.ficheID
INNER JOIN dbo.TB_disRxList list ON per.perID = list.perID
INNER JOIN dbo.TB_disListMed med ON list.idxList = med.idxList AND med.ficheID = fic.ficheID
INNER JOIN dbo.TB_disConstante cnt ON per.nomID = cnt.cntID
INNER JOIN dbo.TB_disMedicament medi ON med.medID = medi.medID
WHERE fic.ficheNoDossier = 'GAGD070775'
ORDER BY rxDate DESC, per.perID, cnt.cntOrdre

It gives thw following:

2      27 novembre 2005      MATIN      1      1      Advil c:\template\advil500.tmp
2      27 novembre 2005      MATIN      1      2      Aspirine      c:\template\aspirine325.tmp
2      27 novembre 2005      MATIN      1      2      Gravol      c:\template\gravol5.tmp
3      27 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
5      27 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
6      27 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
7      26 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
7      26 novembre 2005      MATIN      1      2      Aspirine      c:\template\aspirine325.tmp
7      26 novembre 2005      MATIN      1      2      Gravol      c:\template\gravol5.tmp
8      26 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
9      26 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
10      26 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
12      25 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
12      25 novembre 2005      MATIN      1      2      Aspirine      c:\template\aspirine325.tmp
12      25 novembre 2005      MATIN      1      2      Gravol      c:\template\gravol5.tmp
13      25 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
14      25 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
15      25 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
16      24 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
16      24 novembre 2005      MATIN      1      2      Aspirine      c:\template\aspirine325.tmp
16      24 novembre 2005      MATIN      1      2      Gravol      c:\template\gravol5.tmp
17      24 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
18      24 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
19      24 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
20      23 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
20      23 novembre 2005      MATIN      1      2      Aspirine      c:\template\aspirine325.tmp
20      23 novembre 2005      MATIN      1      2      Gravol      c:\template\gravol5.tmp
23      23 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
24      23 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
25      23 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
26      22 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
26      22 novembre 2005      MATIN      1      2      Aspirine      c:\template\aspirine325.tmp
26      22 novembre 2005      MATIN      1      2      Gravol      c:\template\gravol5.tmp
27      22 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
28      22 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
29      22 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp
30      21 novembre 2005      MATIN      1      1      Advil      c:\template\advil500.tmp
30      21 novembre 2005      MATIN      1      2      Aspirine      c:\template\aspirine325.tmp
30      21 novembre 2005      MATIN      1      2      Gravol      c:\template\gravol5.tmp
31      21 novembre 2005      MIDI      2      1      Advil      c:\template\advil500.tmp
32      21 novembre 2005      SOUPER      3      2      Gravol      c:\template\gravol5.tmp
33      21 novembre 2005      COUCHER      4      3      Advil      c:\template\advil500.tmp

it's 42 rows. I need to have 28 rows and data have to be somehow merged and should give something like those 4 fields:
2 27 Novembre 2005 MATIN 1;Advil;c:\template\advil500.tmp;2;Aspirine;c:\template\aspirine325.tmp;2;Gravol;c:\template\gravol5.tmp
3 27 Novembre 2005 MIDI 1;Advil;c:\template\advil500.tmp
5 27 Novembre 2005 SOUPER 2;Gravol;c:\template\gravol5.tmp

data has to be formatted that way to send to an automated system. This system will take the fourth field and extract the data accordingly. (quantity, drug name, drug template to load).

so, how can I do something like that??

thanks a lot for your time and help

magento

I guess u need to use group concat .Google it..

SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dominic34

ASKER

thanks for the helpfull replies. Now, I have the following:

SELECT DISTINCT per.perID, per.rxDate,
cnt.cntNom,
cnt.cntOrdre,
STUFF(
(SELECT ';' + CAST(CASE WHEN cntNom = 'MATIN' THEN med.medFreqAM
            WHEN cntNom = 'MIDI' THEN med.medFreqMD
            WHEN cntNom = 'SOUPER' THEN med.medFreqSP
       WHEN cntNom = 'COUCHER' THEN med.medFreqHS
            END AS VARCHAR(5)) + ';' +
      medi.medNom + ';' + medi.medTemplateFile
FROM dbo.TB_disFichePatient fic
INNER JOIN dbo.TB_disRxPeriode per ON fic.ficheID = per.ficheID
INNER JOIN dbo.TB_disRxList list ON per.perID = list.perID
INNER JOIN dbo.TB_disListMed med ON list.idxList = med.idxList AND med.ficheID = fic.ficheID
INNER JOIN dbo.TB_disConstante cnt ON per.nomID = cnt.cntID
INNER JOIN dbo.TB_disMedicament medi ON med.medID = medi.medID
WHERE fic.ficheNoDossier = 'GAGD07077502'
ORDER BY rxDate DESC, per.perID, cnt.cntOrdre
FOR XML PATH('')
)
, 1
, 1
, '') AS Posologie

I have the following errors:

The multi-part identifier "per.perID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "per.rxDate" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "cnt.cntNom" could not be bound.
Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "cnt.cntOrdre" could not be bound.

How can I join those fields??

thanks

Éric Moreau

>>I have the following errors:

concatenate them first in a sub query

ASKER CERTIFIED SOLUTION

ralmada