cross tab query

Question

Hi guys, I have a project table with the following columns

ProjectID, ProjectDescription

and a list data table which has the following columns

ProjectID ListType Date Memo

Every project has many list type entries in the ListType Table.

What I want to do is run a query which will return something like this

ProjectID, ProjectDescription, ListType1, ListType1.date, Listtype1.Memo, ListType2, ListType2.date, ListType.Memo

Againg, Every Project is coonected to a number of list type data. I am using Microsoft SQL 2000. so the Pivot keyword doesnot work

Answer 1

What you will have to do is create dynamic SQL and build additional CASE statements for every ListType. I had to do something similar for work where they want certain information about events. The events would be passed as a comma separated list and then I build the case statements.

I have a stored proc that creates a table from a comma separated list

CREATE PROCEDURE [dbo].[ListToTable]
    @vcList     VARCHAR(8000),
    @vcDelimiter    VARCHAR(8000),
    @TableName      SYSNAME,
    @ColumnName SYSNAME
AS
    SET NOCOUNT ON

    DECLARE @iPosStart  INT,
        @iPosEnd    INT,
        @iLenDelim  INT,
        @iExit      INT,
        @vcStr      varchar(8000),
        @vcSql      varchar(8000)

    SET @iPosStart = 1
    SET @iPosEnd = 1
    SET @iLenDelim = LEN(@vcDelimiter)

    SET @vcSql = 'INSERT ' + @TableName + ' (' + @ColumnName + ') VALUES ('''

    SET @iExit = 0

    WHILE @iExit = 0
    BEGIN
        SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)

        IF @iPosEnd <= 0
        BEGIN
            SET @iPosEnd = LEN(@vcList) + 1
            SET @iExit = 1
        END

        SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)

        EXEC(@vcSql + @vcStr + ''')')

        SET @iPosStart = @iPosEnd + @iLenDelim
    END

    RETURN 0

Then here is the dynamic SQL

DECLARE @Events VARCHAR(8000) = 'Event1,Event2,... Eventn'

CREATE TABLE #Events
    (
     EventName VARCHAR(MAX)
    )

EXEC [ListToTable] 
    @Events
   ,','
   ,'#Events'
   ,'EventName'

DECLARE @strCASE VARCHAR(MAX)
SET @strCase = ''

DECLARE Events_Cursor CURSOR 
FOR
SELECT EventName FROM [#Events] 

OPEN Events_Cursor
DECLARE @EventName VARCHAR(MAX)

FETCH NEXT FROM Events_Cursor INTO @EventName
WHILE ( @@FETCH_STATUS <> -1 ) 
    BEGIN
        IF ( @@FETCH_STATUS <> -2 ) 
            SET @strCase = @strCase + ',CASE WHEN [EventOpportunityProduct].[Name] = ''' + @EventName + ''' THEN EventOppertunityLineItem.[Trial_Status__c] ELSE NULL END [' + @EventName + ']'
        FETCH NEXT FROM Events_Cursor INTO @EventName
    END
CLOSE Events_Cursor
DEALLOCATE Events_Cursor

Then add @strCASE into your SELECT statement for ex.

DECLARE @strSQL VARCHAR(MAX)
SET @strSQL = 'SELECT
    Field1
    ,Field2
    ' + @strCASE + '
   FROM Table'

EXEC (@strSQL)