PeterBaileyUk
asked on
Sql server query
I have this
How can I use the result of wordPosition and add it to the end of [ClientCodeWodePosition]
ive attached a small spreadsheet to show
I am using the most recent version of sql server express
ee.xlsx
SELECT smmt.[MVRIS CODE] + '_' + s.value + '_' AS ClientCodeWordPosition,SMMT.[MVRIS CODE], 'SMMT' AS ClientName, s.value AS [Word],LEN(s.VALUE) AS WordLength, CONCAT(ISNULL(RTRIM(LTRIM(SMMT.[Model Range])),''), CHAR(32), ISNULL(RTRIM(LTRIM(SMMT.[RANGE SERIES])),''), CHAR(32), ISNULL(RTRIM(LTRIM(SMMT.[VARIANT])),'')) AS ConcatDesc,
row_number() over (partition by SMMT.[MVRIS CODE] order by LEN(s.VALUE)) WordPosition,
count(*) over (partition by SMMT.[MVRIS CODE]) NoOfWords,SMMT.MARQUE
FROM ClientData.[dbo].SMMT SMMT
CROSS APPLY STRING_SPLIT(CONCAT(ISNULL(RTRIM(LTRIM(SMMT.[Model Range])),''), CHAR(32), ISNULL(RTRIM(LTRIM(SMMT.[RANGE SERIES])),''), CHAR(32), ISNULL(RTRIM(LTRIM(SMMT.[VARIANT])),'')), CHAR(32)) s
WHERE smmt.MARQUE='PEUGEOT' AND SMMT.[MODEL RANGE]='ElDDIS' AND SMMT.[MVRIS CODE]='D39GA' AND s.VALUE <>' 'How can I use the result of wordPosition and add it to the end of [ClientCodeWodePosition]
ive attached a small spreadsheet to show
I am using the most recent version of sql server express
ee.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
clientcodewordposition is the concatenation of the clientcode + _ + s.value +_ + wordposition
ASKER
i hadnt completed that part of the clientcodewordposition as I didnt know how
At line 1, try:
Select ClientCodeWordPosition + '_' + cast(WordPosition As Varchar(5))
brb
Select ClientCodeWordPosition + '_' + cast(WordPosition As Varchar(5))
brb
ASKER
got it
Select ClientCodeWordPosition + '_' + CAST(WordPosition AS NVARCHAR(50))ASKER
aha we crossed
ASKER
thank you, I shall try in future to follow your layout too
ASKER
the error
Msg 8114, Level 16, State 5, Line 4
Error converting data type nvarchar to bigint.