November 15, 2010 at 1:39 pm
How to code to list columns' name for one table separate by ','? Like below:
ID, Fname, Lname, [order date]...
November 15, 2010 at 2:12 pm
Something like this?
USE [AdventureWorks]
SELECT
STUFF(
(SELECT ', ' +name
FROM sys.columns
WHERE OBJECT_ID=OBJECT_ID('Person.Address') FOR XML PATH('')
),1,2,'')
November 16, 2010 at 7:12 am
It works great! Thank you.
November 16, 2010 at 10:34 am
Glad I could help. 😀
I expect you understand how it works, otherwise you shouldn't run that code (like any other code based off the internet).
Example: what would need to be changed if
SELECT ', ' is replaced with SELECT ',' (space after the comma removed)?
A good way to understand that code is to run it step by step starting with the inner SELECT without the FOR XML part...
November 16, 2010 at 11:15 am
One thing that is missing from the query is ordering. Do you want the column names ordered alphabetically, or by their position in the table, or is random acceptable?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 16, 2010 at 11:29 am
WayneS (11/16/2010)
One thing that is missing from the query is ordering. Do you want the column names ordered alphabetically, or by their position in the table, or is random acceptable?
Good point.
I guess that's an even better example to verify the code is understood 😉
November 16, 2010 at 11:43 am
LutzM (11/16/2010)
WayneS (11/16/2010)
One thing that is missing from the query is ordering. Do you want the column names ordered alphabetically, or by their position in the table, or is random acceptable?Good point.
I guess that's an even better example to verify the code is understood 😉
Yes indeed - I was thinking that I should have included it just as I clicked Post...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 16, 2010 at 12:10 pm
Thank you again for your advice.
The only thing I do not understand is "FOR XML PATH('')". What is use for?
November 16, 2010 at 12:11 pm
here is the method I use:
select Case when ROW_NUMBER() over (order by column_id) > 1 then ';' else '' end+C.name
from sys.sysobjects O
join sys.columns C on O.id = C.[object_id]
where O.type = 'U' and O.name = 'YourTableName'
order by column_id
for xml path('')
The probability of survival is inversely proportional to the angle of arrival.
November 16, 2010 at 12:19 pm
adonetok (11/16/2010)
Thank you again for your advice.The only thing I do not understand is "FOR XML PATH('')". What is use for?
Did you try to run the statement step by step?
Something like
USE [AdventureWorks]
SELECT ', ' +name
FROM sys.columns
WHERE OBJECT_ID=OBJECT_ID('Person.Address')
SELECT ', ' +name
FROM sys.columns
WHERE OBJECT_ID=OBJECT_ID('Person.Address') FOR XML PATH('')
SELECT
(SELECT ', ' +name
FROM sys.columns
WHERE OBJECT_ID=OBJECT_ID('Person.Address') FOR XML PATH('')
)
SELECT
STUFF(
( SELECT ', ' +name
FROM sys.columns
WHERE OBJECT_ID=OBJECT_ID('Person.Address') FOR XML PATH('')
),1,2,'')
Edit: ... and check BOL (BooksOnLine, the SQL Server help system usually installed together with SQL Server) for the purpose of FOR XML PATH.
Edit 2: You might want to play with it a little bit... Try to use FOR XML PATH('play_with_it') for instance. 😉
November 16, 2010 at 12:25 pm
Ok, now I understand. Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply