bestway to combine column values

  • I am not sure if this is the right place (I could not find anyplace with sql 2000)to post a sql 2000 question, let me know if there is a place for me to post. Anyway, I have a sql 2000 table like this:

    ID           col1        col2 

    7777      itemx       12/02/07 00:00:10

    7777      itemy       12/02/07 10:00:00

    7777      itemz        12/02/07 12:10:60

    8888      itemA       12/02/07  01:01:00

    888       itemB        12/02/07  02:00:00

    .......................................................

    I like to combine all rows with the same ID together like the followings:

    7777    itemx  itemy   itemz    12/02/07

    888      itemA  itemB.........................

    The question has 3 parts:

    1) what is the best way to do this? using trigger/stored procedure or just a vb sub with somesort of datarepeater, datalist controls

    2) can you show me some examples of each way if any?

    3) Can crystal report do something like this?

    I am open to create another table or just plain writting them out on page.

     

    Thanks

  • This is something like cross-tab report technique. Do try script below:

    DECLARE @Col1 Varchar(50)

    DECLARE @SQL Varchar(5000)

    DECLARE @Num INT

    DECLARE Fields CURSOR FAST_FORWARD FOR

    SELECT Col1 FROM TestItem

    GROUP BY Col1

    ORDER BY Col1

    SET @SQL = ''

    SET @Num = 0

    OPEN Fields

    FETCH NEXT FROM Fields INTO @Col1

    WHILE @@FETCH_STATUS = 0

        BEGIN

     SET @Num = @Num + 1

     IF @Num = 1

      SET @SQL = 'CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),Col1),''NA'') = ''' + @Col1 + ''' THEN [ID] ELSE NULL END)=0 THEN '''' ELSE ''' + @Col1 + ''' END

     '

     ELSE

          SET @SQL = @SQL + '+ CASE WHEN COUNT(CASE WHEN ISNULL(CONVERT(varchar(100),Col1),''NA'') = ''' + @Col1 + ''' THEN [ID] ELSE NULL END)=0 THEN '''' ELSE ''' + @Col1 + ''' END

     '

     FETCH NEXT FROM Fields INTO @Col1

        END

    CLOSE Fields DEALLOCATE Fields

    SET @SQL = 'SELECT ID, ' + @SQL + ' ,Col2

    FROM TestItem

    GROUP BY ID, Col2

    ORDER BY ID, Col2

    '

    --PRINT @SQL

    EXEC (@SQL)

  • Sorry, you need this script for table and data population:

    -- Create table [TestItem]

    if exists (select * from dbo.sysobjects where id = object_id(N'[TestItem]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    drop table [TestItem]

    Create Table TestItem ([ID] INT NULL, Col1 Varchar(20) NULL, Col2 DateTime NULL)

    -- Populate data

    Insert Into TestItem

    SELECT 7777,'itemx','12/Feb/2007' Union All

    SELECT 7777,      'itemy',       '12/Feb/2007' Union All

    SELECT 7777,      'itemz',        '12/Feb/2007' Union All

    SELECT 8888,      'itemA',       '12/Feb/2007' Union All

    SELECT 888,       'itemB',        '12/Feb/2007'

  • Thanks Terry, I will give it a try.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply