SQL select row into a string variable without knowing columns
- by Brandi
Hello,
I am new to writing SQL and would greatly appreciate help on this problem. :)
I am trying to select an entire row into a string, preferably separated by a space or a comma. I would like to accomplish this in a generic way, without having to know specifics about the columns in the tables.
What I would love to do is this:
DECLARE @MyStringVar NVARCHAR(MAX) = ''
@MyStringVar = SELECT * FROM MyTable WHERE ID = @ID AS STRING
But what I ended up doing was this:
DECLARE @MyStringVar = ''
DECLARE @SecificField1 INT
DECLARE @SpecificField2 NVARCHAR(255)
DECLARE @SpecificField3 NVARCHAR(1000)
...
SELECT @SpecificField1 = Field1, @SpecificField2 = Field2, @SpecificField3 = Field3 FROM MyTable WHERE ID = @ID
SELECT @StringBuilder = @StringBuilder + CONVERT(nvarchar(10), @Field1) + ' ' + @Field2 + ' ' + @Field3
Yuck. :(
I have seen some people post stuff about the COALESCE function, but again, I haven't seen anyone use it without specific column names.
Also, I was thinking, perhaps there is a way to use the column names dynamically getting them by:
SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
It really doesn't seem like this should be so complicated. :(
What I did works for now, but thanks ahead of time to anyone who can point me to a better solution. :)
EDIT:
Got it fixed, thanks to everyone who answered. :)
