Unanswered: Adding Multiple Values into a row/column help
Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row
basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.
I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.
The following example will collect the values from a column in a select statement and create a delimited list from the values.
This will denormalize the values from the source table into a single column so the destination table will not meet the requirements of first normal form. This may be best used for reporting operations, that said:
Two tables are created, one to hold values for the list, and one where the results are inserted.
Test values are inserted into the test table and then a select statement collects the values. (Example supports only 4000 characters)
--Create Test Table
CREATE TABLE dbo.test (
dataField NVARCHAR(10) NOT NULL,
PRIMARY KEY (dataField)
--Create Results Table
CREATE TABLE dbo.testResults (
resultId INT IDENTITY (1,1) NOT NULL,
result NVARCHAR(4000) NOT NULL,
PRIMARY KEY (resultId)
--Insert Test Data
INSERT dbo.test (dataField) values ('here')
INSERT dbo.test (dataField) values ('there')
INSERT dbo.test (dataField) values ('everywhere')
--Verify Test Data
SELECT dataField FROM dbo.test
--Retrieve colon delimited list of dataField without a cursor
DECLARE @collectValues NVARCHAR(4000)
SET @collectValues = ('')
@collectValues = @collectValues + dataField + ';'
--Verify delimited list
--Insert into result table
--Verify inserted data
SELECT resultId, result FROM dbo.testResults
The last select statement should return the result value:
Im confused, this doesnt seem like I could get the results correctly from this, You could just use one single select to get all the data like that from that, but what if this works as above, then how would it diferentiate from members and there intrests. Let me give an exampe. Member1 has intrests of fishing,boating,camping, member2 has intrests of fising,hiking,running, how would i basically convert the below