May 12, 2008 at 12:31 pm
I cant seem to get the syntax right, anyone help? it returns the error :-
Line 5 Incorrect syntax near ''.
declare @sql nvarchar(1000)
select @sql = '' create table ey_report_temp (
county nvarchar(100),
fname nvarchar(100), '' + STUFF((SELECT distinct '','' + sport + '' nvarchar(10) ''
FROM ey_report
FOR XML PATH('''')
), 1, 1, '''') + '')''
SQL2000
May 12, 2008 at 1:45 pm
I don't understand what you are trying to do?
If you want to create a table, you create column with data type and length.
When you do the SELECT distinct statement , it may come back with multiple rows, so you want to create a table with multiple columns? Besides I don't think you can put a select statement in a create table statement.
May 12, 2008 at 2:22 pm
I think this is what you are looking for:
[font="Courier New"]DECLARE @sql NVARCHAR(1000)
SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' +
STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(10) ' FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'
SELECT @sql
[/font]
I added the "[" & "]" because if you have spaces or reserved words in the sport column you need to qualify them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 12, 2008 at 3:51 pm
Do you want to get rid of the space so you use the STUFF command?
Why don't you use LTRIM?
May 12, 2008 at 9:24 pm
It's the leading comma from the XML concatenation that they're trying to get rid of.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply