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
Dominic34Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

magentoCommented:
I guess u need to use group concat .Google it..
Éric MoreauSenior .Net ConsultantCommented:
in your first query, concatenate the fields (quantity, drug name, drug template to load) into a single field.
then check http://www.emoreau.com/Entries/Blogs/2009/10/SQL-Concatenate-values-multiple-from-multiple-lines.aspx to summarize them.
Dominic34Author Commented:
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
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Éric MoreauSenior .Net ConsultantCommented:
>>I have the following errors:

concatenate them first in a sub query
ralmadaCommented:
try the below
;with CTE as (
SELECT  
	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'
)
select distinct
	a.perID,
	a.rxDate,
	a.cntNom,
	a.cntOrdre,
	stuff(
		(select '; ' + cast(Quantite as varchar) + ',' + medNom + ',' + medtemplateFile from CTE where perID = a.perID for xml path('')),
		1, 2, '') as combinedrows
from CTE a
ORDER BY a.rxDate DESC, a.perID, a.cntOrdre

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dominic34Author Commented:
works like a charm!!! thanks!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.