Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    77

    Unanswered: transpose rows into columns

    Any of you have code to transpose rows into columns in T-SQL 2000. For example I have the following table

    ID type
    1 A
    1 B
    2 A
    2 C
    2 D
    etc,....




    I want to tranpose

    to some like

    ID type
    1 A,B
    2 A,C,D
    etc,...


    or

    ID type1 type2 type3
    1 A B
    2 A C D

    etc,....


    Really appreciate any input.

  2. #2
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Try this..............

    CREATE FUNCTION GetConcatenated(@ID VARCHAR(34)) RETURNS VARCHAR(2048)
    AS
    BEGIN
    DECLARE @ConcType VARCHAR (2048)

    SELECT @ConcType = ISNULL(@ConcType,'') + Type
    FROM TableName
    WHERE
    ID = @ID

    RETURN @ConcType
    END
    GO


    SELECT GetConcatenated(ID), TYPE FROM TABLENAME

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by DBA_Rahul
    Try this..............

    Expensive, very expensive
    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.

  4. #4
    Join Date
    Mar 2010
    Posts
    1
    Best to limit to small scale for this sort of thing. If you're using SQL 2k though and don't have PIVOT features available, I've drafted a stored proc that should do the job for you. Bit of a botch rush job so pull it apart as much as you like. Paste the below into a sql window and edit the EXEC at the bottom as preferred. If you want to see what's being generated, remove the --s in the middle:





    IF EXISTS (SELECT * FROM SYSOBJECTS WHERE XTYPE = 'P' AND NAME = 'USP_LIST_CONCAT')
    DROP PROCEDURE USP_LIST_CONCAT
    GO

    CREATE PROCEDURE USP_LIST_CONCAT (@SourceTable NVARCHAR(1000) = '' ,@SplitColumn NVARCHAR(1000) = '' , @Deli NVARCHAR(10) = '', @KeyColumns NVARCHAR(2000) = '' , @Condition NVARCHAR(1000) = '')
    AS
    BEGIN
    SET NOCOUNT ON

    /* PROCEDURE CREATED 2010 FOR SQL SERVER 2000. SIMON HUGHES. */
    /* NOTES: REMOVE --'s BELOW TO LIST GENERATED SQL. */

    IF @SourceTable = '' OR @SourceTable = '?' OR @SourceTable = '/?' OR @SplitColumn = '' OR @KeyColumns = ''
    BEGIN
    PRINT 'Format for use:'
    PRINT ' USP_LIST_CONCAT ''SourceTable'', ''SplitColumn'', ''Deli'', ''KeyColumn1,...'', ''Column1 = 12345 AND ...'''
    PRINT ''
    PRINT 'Description:'
    PRINT 'The SourceTable should contain a number of records acting as a list of values.'
    PRINT 'The SplitColumn should be the name of the column holding the values wanted.'
    PRINT 'The Delimiter may be any single character or string ie ''/'''
    PRINT 'The KeyColumn may contain a comma seperated list of columns that will be returned before the concatenated list.'
    PRINT 'The optional Conditions may be left blank or may include the following as examples:'
    PRINT ' ''Column1 = 12334 AND (Column2 = ''ABC'' OR Column3 = ''DEF'')'''
    PRINT ''
    PRINT 'A standard list in the format:'
    PRINT ' Store1, Employee1, Rabbits'
    PRINT ' Store1, Employee1, Dogs'
    PRINT ' Store1, Employee1, Cats'
    PRINT ' Store1, Employee2, Dogs'
    PRINT ''
    PRINT 'Will be returned as:'
    PRINT ' Store1, Employee1, Cats/Dogs/Rabbits'
    PRINT ' Store1, Employee2, Dogs'
    PRINT ''
    PRINT 'A full ORDER BY and DISTINCT is included'
    RETURN -1
    END


    DECLARE @SQLStatement NVARCHAR(4000)

    SELECT @SQLStatement = '
    DECLARE @DynamicSQLStatement NVARCHAR(4000)

    SELECT @DynamicSQLStatement = ''SELECT '+@KeyColumns+', SUBSTRING(''

    SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' + '' + CHAR(10) +
    '' MAX(CASE WHEN '+@SplitColumn+' = ''''''+RTRIM('+@SplitColumn+')+'''''' THEN '''''+@Deli+'''+RTRIM('+@SplitColumn+')+'''''' ELSE '''''''' END)''
    FROM '+ @SourceTable +' ORDER BY '+@SplitColumn+'

    SELECT @DynamicSQLStatement = @DynamicSQLStatement + '' ,2,7999) List'' + CHAR(10) + ''FROM '+ @SourceTable+''' + CHAR(10) +'''+CASE WHEN @Condition = '' THEN '/* WHERE */' ELSE 'WHERE '+@Condition END+ '''+ CHAR(10) + ''GROUP BY '+@KeyColumns+'''

    SELECT @DynamicSQLStatement = REPLACE(@DynamicSQLStatement,''( +'',''('')

    -- SELECT @DynamicSQLStatement -- DEBUG ONLY
    EXEC (@DynamicSQLStatement)'

    EXEC (@SQLStatement)

    END
    GO

    EXEC USP_LIST_CONCAT 'MyTableName', 'ColumnForListing', 'Delimiter', 'KeyCol1, KeyCol2', 'Column1 = 123456'

Posting Permissions

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