• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
PortletPaulCommented:
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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