• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

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

output
0
PeterBaileyUk
Asked:
PeterBaileyUk
  • 6
  • 5
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You only want a single line returned by the 2nd query?
0
 
PeterBaileyUkAuthor Commented:
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
0
 
PeterBaileyUkAuthor Commented:
the sequence in this example for client code 54553002 is 260, 358, 283, 138
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
So you want the sequence to be in the 1st query.
2nd query is only to show us the data, right?
0
 
PeterBaileyUkAuthor Commented:
yes exactly that. i couldnt see how to do it
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
PeterBaileyUkAuthor Commented:
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
0
 
PeterBaileyUkAuthor Commented:
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

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I assumed (wrong) they're all varchar. So you'll need to convert it:
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 ', ' + CAST(AsciiEncode AS VARCHAR)
          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

0
 
PeterBaileyUkAuthor Commented:
thats amazing thank you, i will spend some time looking at this so i understand it better, thank you.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
1
 
PortletPaulfreelancerCommented:
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.  embed-an-image-2.png
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now