Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004

    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.


  2. #2
    Join Date
    Jan 2004

    Delimited List Without Cursor

    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 + ';'
    FROM dbo.test
    --Verify delimited list
    SELECT @collectValues
    --Insert into result table
    INSERT dbo.testResults
    --Verify inserted data
    SELECT resultId, result FROM dbo.testResults
    The last select statement should return the result value:
    Last edited by gallon; 03-07-04 at 16:55.

  3. #3
    Join Date
    Feb 2004
    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

    table one

    customer intrest

    member1 fishing
    member1 boating
    member1 camping
    member2 fishing
    member2 hiking
    member2 running

    go from that data, to this data

    table two

    customer intrests
    member1 fishing;boating;bamping
    member2 fising;hiking;running

    I dont think the above example can do this can it? Or Am I just missing something? Thanks! hehe

  4. #4
    Join Date
    Jan 2004
    You are absolutely correct. I misread your intention as wanting the value for a single person (as though you would add this update to a procedure for updating the base table, etc...).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts