SQL Server Performance

TSQL assistance please

Discussion in 'SQL Server 2005 General Developer Questions' started by sql_jr, May 30, 2007.

  1. sql_jr New Member

    I need to basically rollup some info from a table, and not sure how. Say I have table data that looks like:

    Col1 Col2 Col3 Col4
    ----------------------
    11 A Null Null
    11 B C Null
    11 Null Null E

    So, Col1 is the common identifier, and I'd want to select and get:

    A,B,C,E

    Please advise....thx a mil!
  2. Adriaan New Member

    Step 1 is to do

    create table #t (col1 varchar(10), col2 varchar(10))

    INSERT INTO #t
    SELECT Col1, Col2 FROM tbl
    UNION SELECT Col1, Col3 FROM tbl
    UNION SELECT Col1, Col4 FROM tbl

    Step 2 is to do a cross-tab on your #t temp table. DO a search for cross-tab.
  3. sql_jr New Member

    thx, is there a way to join the table to itself, and get it into one row that way? (I'm a little tsql challenged <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  4. Adriaan New Member

    Self join?

    SELECT t1.*, t2.*
    FROM tbl t1
    INNER JOIN tbl t2
    ON t1.pkcol = t2.pkcol

    I mixed up my answer - you don't need a cross-tab but a UDF ...

    CREATE FUNCTION dbo.fnRollUpFromTbl (@KeyID INT)
    RETURNS VARCHAR(8000)
    AS

    DECLARE @Return VARCHAR(8000)

    SET @Return = ''

    SELECT @Return = @Return + CASE WHEN LEN(@Return) = 0 THEN '' ELSE ',' END + t.Col2
    FROM
    (SELECT tbl.Col2 FROM tbl WHERE tbl.Col1 = @KeyId AND tbl.Col2 <> ''
    UNION SELECT tbl.Col3 FROM tbl WHERE tbl.Col1 = @KeyId AND tbl.Col3 <> ''
    UNION SELECT t.Col4 FROM tbl WHERE tbl.Col1 = @KeyId AND tbl.Col4 <> '') t

    RETURN (@Return)

    GO

Share This Page