query

• bryan99y

  SSCarpal Tunnel

  Points: 4672

  More actions

  June 13, 2003 at 1:37 pm

  #106574

  Do you know how to modify this query, so that x + y are returned as column string instead of rows?

  so instead of

  geoidxy

  10034

  10059

  100945

  we have

  1003,4,5,9,9,45

• jpipes

  SSChampion

  Points: 12230

  More actions

  June 13, 2003 at 1:53 pm

  #461381

  
  
  CREATE TABLE #temp1 (key INT , string VARCHAR(10))
  
  INSERT INTO #temp1
  
  SELECT geoid, CAST(x AS varchar(3)) + ',' + CAST(y AS varchar(3)
  
  FROM my_table
  
  

  
  
  CREATE TABLE #temp2 (key INT , string VARCHAR(100))
  
  DECLARE @k INT, @temp_string VARCHAR(100)
  
  DECLARE c CURSOR FOR
  
  SELECT key FROM #temp1
  
  OPEN c
  
  FETCH NEXT c INTO @k
  
  WHILE @@FETCH_STATUS = 0 BEGIN
  
    -- Concatenate strings for each key
  
    SELECT @temp_string = @temp_string + string + ','
  
    FROM #temp1
  
    WHERE key = @k
  
    -- Cut off last comma
  
    SET @temp_string = SUBSTRING(@temp_string, 1, LENGTH(@temp_string - 1))
  
    -- Next insert concatenation
  
    INSERT INTO #temp2
  
    VALUES (@k, @temp_string)
  
    -- Oops, probably want to reset temp_string:
  
    SET @temp_string = ''
  
    -- Get the next key
  
    FETCH NEXT c INTO @k
  
  END
  
  CLOSE c
  
  DEALLOCATE c
  
  

  I did this real quick, so sorry in advance for any typos...

  Jay

  Edited by - jpipes on 06/13/2003 1:54:00 PM

  Edited by - jpipes on 06/13/2003 1:55:58 PM

  Edited by - jpipes on 06/13/2003 1:57:51 PM