Solved

sql server query

Posted on 2016-09-15
12
53 Views
Last Modified: 2016-09-15
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
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41799352
You only want a single line returned by the 2nd query?
0
 

Author Comment

by:PeterBaileyUk
ID: 41799362
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
 

Author Comment

by:PeterBaileyUk
ID: 41799363
the sequence in this example for client code 54553002 is 260, 358, 283, 138
0
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41799369
So you want the sequence to be in the 1st query.
2nd query is only to show us the data, right?
0
 

Author Comment

by:PeterBaileyUk
ID: 41799379
yes exactly that. i couldnt see how to do it
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41799385
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
 

Author Comment

by:PeterBaileyUk
ID: 41799391
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
 

Author Comment

by:PeterBaileyUk
ID: 41799393
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
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41799397
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 41799421
thats amazing thank you, i will spend some time looking at this so i understand it better, thank you.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41799432
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 41799614
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question