asked on

sql server query

I have 2 queries that show me the clientcode and word data. I would like to get the [wordposition] & [ÀsciiEncode] horizontalised so I can see in the first query this:
'260,358,283,138' this being the ascii sequence order following the wordposition.

The sql so far:

select tw.ClientName, TW.StrShort, TW.clientcode, sum(CWC.asciiencode) as AsciiEncodeTotal, sum(CWC.AsciiEncodeClientCode) as AsciiEncodeCCodeTotal, sum(CWC.WordLength) as WordLengthTotal, sum(CWC.NoOfWords) As WordTotal
from TblCurrentWordCounts CWC left join tblwords TW on CWC.clientcode=TW.ClientCode
where TW.StrShort like'xc90%' and tw.clientcode='54553002'  --and TW.ClientName='Smmt'--and TW.StrShort like'%es%118i%' 
group by CWC.clientcode, TW.ClientCode,TW.StrShort,tw.ClientName
order by tw.ClientName

select TW.ClientCodeWordPosition, TW.ClientCode, tw.AsciiEncode, tw.AsciiEncodeClientCode,  tw.WordPosition
from Tblwords TW
where clientcode='54553002'
group by TW.ClientCode, TW.ClientCodeWordPosition,tw.AsciiEncode, tw.AsciiEncodeClientCode,  tw.WordPosition

Open in new window

Vitor Montalvão

You only want a single line returned by the 2nd query?

PeterBaileyUk

ASKER

no the first one returns one line (the second i showed as that has the word positions and the asci codes together. so you would understand. )and in that I would like the ascii sequence
ee1.JPG

PeterBaileyUk

ASKER

the sequence in this example for client code 54553002 is 260, 358, 283, 138

Vitor Montalvão

So you want the sequence to be in the 1st query.
2nd query is only to show us the data, right?

PeterBaileyUk

ASKER

yes exactly that. i couldnt see how to do it

Vitor Montalvão

Ok. With no data to test I came with this solution so isn't tested:

SELECT tw.ClientName, TW.StrShort, TW.clientcode, sum(CWC.asciiencode) as AsciiEncodeTotal, sum(CWC.AsciiEncodeClientCode) as AsciiEncodeCCodeTotal, sum(CWC.WordLength) as WordLengthTotal, sum(CWC.NoOfWords) As WordTotal,
	STUFF((SELECT ', ' + AsciiEncode 
          FROM Tblwords
          WHERE Tblwords.clientcode=CWC.clientcode
		  ORDER BY WordPosition
          FOR XML PATH('')), 1, 1, '') AsciiOrder
FROM TblCurrentWordCounts CWC 
	LEFT JOIN tblwords TW on CWC.clientcode=TW.ClientCode
WHERE TW.StrShort like'xc90%' and tw.clientcode='54553002'  --and TW.ClientName='Smmt'--and TW.StrShort like'%es%118i%' 
GROUP BY CWC.clientcode, TW.ClientCode,TW.StrShort,tw.ClientName
ORDER BY tw.ClientName

Open in new window

Check if it works.

PeterBaileyUk

ASKER

it gave this error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ', ' to data type int.

ive attached the column data types
ee1.JPG
ee2.JPG

PeterBaileyUk

ASKER

i dont know if this helps

<row>
  <ClientCodeWordPosition>54553002_OCEAN_2</ClientCodeWordPosition>
  <ClientCode>54553002</ClientCode>
  <AsciiEncode>358</AsciiEncode>
  <AsciiEncodeClientCode>956</AsciiEncodeClientCode>
  <WordPosition>2</WordPosition>
</row>
<row>
  <ClientCodeWordPosition>54553002_RACE_3</ClientCodeWordPosition>
  <ClientCode>54553002</ClientCode>
  <AsciiEncode>283</AsciiEncode>
  <AsciiEncodeClientCode>932</AsciiEncodeClientCode>
  <WordPosition>3</WordPosition>
</row>
<row>
  <ClientCodeWordPosition>54553002_T6_4</ClientCodeWordPosition>
  <ClientCode>54553002</ClientCode>
  <AsciiEncode>138</AsciiEncode>
  <AsciiEncodeClientCode>788</AsciiEncodeClientCode>
  <WordPosition>4</WordPosition>
</row>
<row>
  <ClientCodeWordPosition>54553002_XC90_1</ClientCodeWordPosition>
  <ClientCode>54553002</ClientCode>
  <AsciiEncode>260</AsciiEncode>
  <AsciiEncodeClientCode>907</AsciiEncodeClientCode>
  <WordPosition>1</WordPosition>
</row>

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

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

Start Free Trial

PeterBaileyUk

ASKER

thats amazing thank you, i will spend some time looking at this so i understand it better, thank you.

Vitor Montalvão

Actually, that's a very known trick.
It's a combination of STUFF function to remove the first comma and FOR XML PATH keyword to generate the concatenation of all rows in a single string.

PortletPaul

Re-look at the question, the image is now visible without needing a click.

Tip: Use the icon seen above the comment text area to include images. This way it "embeds" it and users don't have to jump to different tabs/pages to see that image.