Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server query

Posted on 2016-09-15
12
Medium Priority
?
55 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 52

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 52

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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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 52

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

722 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