April 23, 2014 at 8:03 am
I have a view that assembles fields from another table into a single string. The result is fairly small, 56 characters max at the moment, although it can get larger. But not very much - 100 characters would be huge, and quite possibly a mistake. However the view delivers its results as a varchar(max), which translates into a Memo field in Access (the application driving this), and so prevents me from using the field in a JOIN clause.
Is there a way to force the result field definition down to something reasonable? I tried adding Left(---,100) to the field in question, but no effect - it's still a max.
SELECT VLTO.SkupinaAutoID, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(STUFF
((SELECT ',' + TC.BPAuthorAbbreviation
FROM dbo.SouhrnyLidi VLTI INNER JOIN
dbo.TableOfCloveks TC ON VLTI.ClovekAutoID = TC.ClovekAutoID
WHERE VLTI.SkupinaAutoID = VLTO.SkupinaAutoID FOR XML PATH('')), 1, 1, ''), '&', '&'), '(,', '('), ',&,', ' & '), ',),', ') '), ',ex,', ' ex '), ',et,', ' et '), ',in,', ' in '), ',,,',
', ') AuthorString
FROM dbo.SouhrnyLidi VLTO
GROUP BY VLTO.SkupinaAutoID
April 23, 2014 at 8:44 am
Can you simply cast your stuffed column as a varchar?
SELECT VLTO.SkupinaAutoID, cast(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(STUFF
((SELECT ',' + TC.BPAuthorAbbreviation
FROM dbo.SouhrnyLidi VLTI INNER JOIN
dbo.TableOfCloveks TC ON VLTI.ClovekAutoID = TC.ClovekAutoID
WHERE VLTI.SkupinaAutoID = VLTO.SkupinaAutoID FOR XML PATH('')), 1, 1, ''), '&', '&'), '(,', '('), ',&,', ' & '), ',),', ') '), ',ex,', ' ex '), ',et,', ' et '), ',in,', ' in '), ',,,',
', ')
as varchar(200)) AuthorString
FROM dbo.SouhrnyLidi VLTO
GROUP BY VLTO.SkupinaAutoID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2014 at 8:47 am
Sean Lange (4/23/2014)
Can you simply cast your stuffed column as a varchar?
SELECT VLTO.SkupinaAutoID, cast(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(STUFF
((SELECT ',' + TC.BPAuthorAbbreviation
FROM dbo.SouhrnyLidi VLTI INNER JOIN
dbo.TableOfCloveks TC ON VLTI.ClovekAutoID = TC.ClovekAutoID
WHERE VLTI.SkupinaAutoID = VLTO.SkupinaAutoID FOR XML PATH('')), 1, 1, ''), '&', '&'), '(,', '('), ',&,', ' & '), ',),', ') '), ',ex,', ' ex '), ',et,', ' et '), ',in,', ' in '), ',,,',
', ')
as varchar(200)) AuthorString
FROM dbo.SouhrnyLidi VLTO
GROUP BY VLTO.SkupinaAutoID
Doh! That did it - many thanks, and please excuse me while I go get my humble pie out of the fridge. :hehe:
April 23, 2014 at 8:57 am
pdanes (4/23/2014)
Sean Lange (4/23/2014)
Can you simply cast your stuffed column as a varchar?
SELECT VLTO.SkupinaAutoID, cast(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(STUFF
((SELECT ',' + TC.BPAuthorAbbreviation
FROM dbo.SouhrnyLidi VLTI INNER JOIN
dbo.TableOfCloveks TC ON VLTI.ClovekAutoID = TC.ClovekAutoID
WHERE VLTI.SkupinaAutoID = VLTO.SkupinaAutoID FOR XML PATH('')), 1, 1, ''), '&', '&'), '(,', '('), ',&,', ' & '), ',),', ') '), ',ex,', ' ex '), ',et,', ' et '), ',in,', ' in '), ',,,',
', ')
as varchar(200)) AuthorString
FROM dbo.SouhrnyLidi VLTO
GROUP BY VLTO.SkupinaAutoID
Doh! That did it - many thanks, and please excuse me while I go get my humble pie out of the fridge. :hehe:
Glad that worked for you. It is often the simple things in a query this complicated that are hard to see. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply