Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Talking Unanswered: About the Complex SQL Statement

    Belows are the data of table T1

    field1 field2 field3 Value
    F1 F2 F3 A
    F1 F2 F3 B
    F1 F2 F3 C
    ... .... .....
    F1 F2 F3 Z


    Can any bright person help to script the SQL to extract above data set and present as below ???

    field1 field2 field3 Value
    F1 F2 F3 ABC......Z

    Thanks for help

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thought this woiuld be a good exercise...WHY you would want to do this, I have no idea....

    Sorry, had to use a cursor.....any non cursor solutions out there?

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 char(2), Col2 char(2), Col3 char(2), Col4 char(1))
    GO
    
    INSERT INTO myTable99(Col1, Col2, Col3, Col4)
         SELECT 'F1', 'F2', 'F3', 'A' UNION ALL
         SELECT 'F1', 'F2', 'F3', 'B' UNION ALL
         SELECT 'F1', 'F2', 'F3', 'C' UNION ALL
         SELECT 'F1', 'F2', 'F3', 'Z' UNION ALL
         SELECT 'F1', 'F2', 'F5', 'A' UNION ALL
         SELECT 'F1', 'F2', 'F6', 'B' UNION ALL
         SELECT 'F1', 'F2', 'F7', 'C' UNION ALL
         SELECT 'F1', 'F2', 'F8', 'Z'
    GO
    
    SET NOCOUNT ON
    
    DECLARE myCursor99 CURSOR 
    FOR
    SELECT DISTINCT Col1, Col2, Col3
      FROM myTable99
    
    DECLARE @Col1 char(2), @Col2 char(2), @Col3 char(2), @Col4 varchar(7000)
    
    DECLARE @x TABLE(Col1 char(2), Col2 char(2), Col3 char(2), Col4 varchar(7000))
    
    OPEN myCursor99
    
    FETCH NEXT 
          FROM myCursor99 
          INTO @Col1
    	 , @Col2
    	 , @Col3
    
    WHILE @@FETCH_STATUS = 0 
      BEGIN
    	     SELECT @Col4 = COALESCE(@Col4+ ', ', '') + CAST(Col4 AS varchar(6))
    	       FROM myTable99
    	      WHERE Col1 = @Col1
    		AND Col2 = @Col2
    		AND Col3 = @Col3
    
    	INSERT INTO @x(Col1, Col2, Col3, Col4)	
    	     SELECT DISTINCT @Col1, @Col2, @Col3, @Col4
    	       FROM myTable99
    	      WHERE Col1 = @Col1
    		AND Col2 = @Col2
    		AND Col3 = @Col3
    
    	FETCH NEXT 
    	      FROM myCursor99 
    	      INTO @Col1
    		 , @Col2
    		 , @Col3
    
    	     SELECT @Col4 = null
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    
    SET NOCOUNT OFF
    
    SELECT * FROM @x
    
    SET NOCOUNT OFF
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    45
    CREATE FUNCTION dbo.f_ConcatVals(@f1 char(2), @f2 char(2), @f3 char(3)) RETURNS varchar(8000) AS BEGIN
    DECLARE @s varchar(8000)
    SET @s = ''
    SELECT @s = @s + Value
    FROM T1
    WHERE Field1 = @f1 AND Field2 = @f2 AND Field3 = @f3
    ORDER BY Value
    RETURN @s END

    SELECT DISTINCT Field1, Field2, Field3, dbo.f_ConcatVals(Field1,Field2,Field3) Value
    FROM t1
    Hans.

Posting Permissions

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