locked
How to Pivot tables in SQL 2000 RRS feed

  • Pergunta

  • Hi All,

    I've a query that is designed using PIVOT as discussed, http://j.mp/zV6tVa. Now this query has to be executed on SQL 2000. It is a known fact that pivot is not a feature in SQL 2000.

    How can we do the pivoting on the below table structure.

    Table 1 : TableColumnsDef

    CREATE TABLE TableColumns(
    	[ColID] [int] IDENTITY(1,1) NOT NULL,
    	[ColDisplayName] [nvarchar](50) NOT NULL,
    	[ColDescription] [nvarchar](100) NULL
    	)

    Table 2: TableColumnVals

    CREATE TABLE TableColumnVals(
    	[ColID] [int] NOT NULL,
    	[RowIDVal] [bigint] NOT NULL,
    	[ColValue] [nvarchar](500) NOT NULL	
    	)
    
    Apart of all, is there any other way that we can do that with TempTable variable in StoredProcedure?

    Thanks for reading


    Every thing is perfect as long as you share

    terça-feira, 28 de fevereiro de 2012 09:53

Respostas

Todas as Respostas

  • Check this thread. It has an example of PIVOTing without PIVOT in SQL 2000.

    http://stackoverflow.com/questions/312861/pivot-using-sql-server-2000


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    terça-feira, 28 de fevereiro de 2012 10:49
  • This solution works as long as if you know that there are not going to be new columns for any given product. What if they add D column to the product, you have write a new CASE for D.

    Please note that, in the given situation by me, the columns are dynamic and I dont have to hard code any value.

    Anyhow, thanks for reading and quickly replying


    Every thing is perfect as long as you share

    terça-feira, 28 de fevereiro de 2012 10:57
  • CREATE TABLE TableTable(Query text) where Query is to contain your sql 2000 pivoting .

    Create trigger for insert, update, delete on  TableColumns  which would rebuild this pivoting Query.

    Dynamically execute Query when retreiving values.


    Serg

    terça-feira, 28 de fevereiro de 2012 11:27
  • You might dynamically generate the case statements and plug it into the query.

    Execute the dynamic query then. This could solve the problem.

    Just an idea. No data. So couldn't test this. It's compilable though.

    -- Initial
    SELECT [p].ProductID, [p].Name,
    CASE [m].MetaKey WHEN 'A' THEN [m].MetaValue END AS A,
    CASE [m].MetaKey WHEN 'B' THEN [m].MetaValue END AS B,
    CASE [m].MetaKey WHEN 'C' THEN [m].MetaValue END AS C
    FROM Products [p]
    INNER JOIN ProductMeta [m]
    ON [p].ProductId = [m].ProductId
    GROUP BY [p].ProductID, [p].Name
    
    --New
    DECLARE @sql varchar(5000)
    DECLARE @rval varchar(5000)
    SELECT @rval = COALESCE(@rval + ',','') + 'CASE [m].MetaKey WHEN ' + '''' + [m].MetaKey + '''' + ' THEN [m].MetaValue END AS ' + CAST([m].MetaKey AS VARCHAR)
     FROM ProductMeta [m]
    SELECT @rval
    
    SELECT @sql = 'SELECT [p].ProductID, [p].Name,' + @rval + 'FROM Products [p]
    INNER JOIN ProductMeta [m]
    ON [p].ProductId = [m].ProductId
    GROUP BY [p].ProductID, [p].Name'
    
    EXEC(@sql)
    Hope this works.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    terça-feira, 28 de fevereiro de 2012 11:30
  • You mean to say that I've to construct

    SELECT @rval = COALESCE(@rval + ',','') + 'CASE [m].MetaKey WHEN ' + '''' + [m].MetaKey + '''' + ' THEN [m].MetaValue END AS ' + CAST([m].MetaKey AS VARCHAR)
    

    the above line dynamically in SP?

    (or)

    The given command will take care of itself?

    Apart of that mentioned example from the other link, can we discuss on my table structure!!??


    Every thing is perfect as long as you share

    terça-feira, 28 de fevereiro de 2012 11:56
  • I'm not following what you mentioned in these lines. Can you elaborate?

    Every thing is perfect as long as you share

    terça-feira, 28 de fevereiro de 2012 11:58
  • Chakravarthy,

    I belive, you are looking for Naomi's solution: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/dynamic-pivot-on-multiple-columns

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.
    My Blog

    • Sugerido como Resposta Peja Tao quarta-feira, 29 de fevereiro de 2012 03:04
    • Não Sugerido como Resposta Chakravarthy quarta-feira, 29 de fevereiro de 2012 11:23
    • Marcado como Resposta Kalman Toth sábado, 17 de março de 2012 13:46
    terça-feira, 28 de fevereiro de 2012 12:18
  • COALESCE

    This function will provide the comma separated case statements. You plug the generated case statements into your select statement to generate the final query to be executed.

    Naomi's solution proposed above has a good description of solving this method.

    Hope this helps.


    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu


    • Editado arun.passioniway terça-feira, 28 de fevereiro de 2012 13:26 minor edit
    terça-feira, 28 de fevereiro de 2012 13:06
  • @Chakravarthy

    1.Definitely you need dynamic sql in your code  because number of columns isn't known when writing the code.

    2. Constructing dynamic sql every time you need a report or something is time consuming. Fortunatley, this dynamic part depending on set of columns needs to be dynamic only when you change  TableColumns any way. So a trigger on this table is a good place to build and save this dynamic part of your queries.


    Serg

    terça-feira, 28 de fevereiro de 2012 13:16
  • Interesting idea - can you elaborate a bit - where do you plan to store the dynamic part?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    terça-feira, 28 de fevereiro de 2012 14:06
  • Exactly what Janos and Arun said - you should be able to dynamically construct the query. The key thing here is to know the final query you're going to arrive to - then a few trial and errors and printing statements until  you get it right. That's my approach of writing dynamic queries. The blog post should help you going in the right direction.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    terça-feira, 28 de fevereiro de 2012 14:09
  • Interesting idea - can you elaborate a bit - where do you plan to store the dynamic part?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    OR already has one dictionary table, TableColumns, about columns. Just add one more table about table[s].


    Serg

    terça-feira, 28 de fevereiro de 2012 14:18
  • I see, you're suggesting something like

    Table varchar(128), DynamicTableColumns varchar(8000) 

    and populate this field (update) every time the change is done in TableColumns table? 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    terça-feira, 28 de fevereiro de 2012 14:23
  • Yes, but ajust data types to  SQL 2000, it has only varchar(4000) if remember it right. So may be

    DynamicTableColumns text,

    or

    DynamicTableColumns01 varchar(4000)

    , DynamicTableColumns02 varchar(4000)

    , ...


    Serg

    quarta-feira, 29 de fevereiro de 2012 08:33
  • No, this is not correct. NVARCHAR max is 4000, VARCHAR max is 8000 in SQL 2000.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marcado como Resposta Kalman Toth sábado, 17 de março de 2012 13:46
    quarta-feira, 29 de fevereiro de 2012 13:37
  • You can use the CASE expression for "PIVOT"-ing in SQL Server 2008:

    http://www.sqlusa.com/bestpractices/training/scripts/casefunction/

    Example for dynamic CASE crosstab query:

    http://www.sqlusa.com/bestpractices2008/crosstab/

    Note: SSRS has built-in dynamic PIVOT. A real beauty.


    Kalman Toth, SQL Server & Business Intelligence Training;  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016




    • Editado Kalman Toth quinta-feira, 9 de novembro de 2017 15:34
    terça-feira, 6 de março de 2012 12:45
  • Kalman Toth,

    I understand the point that you are mentioning. But the fact that you are missing here is the construction of these CASEs depending on the number of rows that are pumped into the TableColumns table. Please read the other chain of discussion also.

    Anyhow, thanks for the reply and the article.


    Every thing is perfect as long as you share

    terça-feira, 6 de março de 2012 13:51
  • The blog post that Janos mentioned provides an example of constructing case based pivot dynamically.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    terça-feira, 6 de março de 2012 14:01
  • Kalman Toth,

    I understand the point that you are mentioning. But the fact that you are missing here is the construction of these CASEs depending on the number of rows that are pumped into the TableColumns table.

    The following article has example for dynamic CASE expression PIVOT:

    http://www.sqlusa.com/bestpractices2008/crosstab/

    Dynamic SQL article:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    Worthwhile to note: SSRS has built-in easy-to-use dynamic PIVOT.


    Kalman Toth  Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016



    • Marcado como Resposta Kalman Toth sábado, 17 de março de 2012 13:47
    • Editado Kalman Toth quinta-feira, 9 de novembro de 2017 15:35
    segunda-feira, 12 de março de 2012 15:31
  • you can try this code



    CREATE TABLE [dbo].[Products](
    [productid] [int] NOT NULL,
    [Name] [NVARCHAR](50) NULL 
    )
    --product Meta 
    CREATE TABLE [dbo].[ProductMeta](
    [Id] [int] NOT NULL,
    [ProductId] [int] NULL,
    [MetaKey] [NVARCHAR](50) NULL,
    [MetaValue] [float] NULL


    DECLARE @strsql NVARCHAR(4000),@substr AS NVARCHAR(2000)

    DECLARE @myId INT ,@mypathch NVARCHAR(256)
    SET @substr=''

    DECLARE @d cursor
    SET @d=cursor Fast_forward for
     SELECT  metakey AS mypathch FROM ProductMeta GROUP BY  metakey ORDER BY metakey

    OPEN @d 
    FETCH FROM @d INTO @mypathch 
    WHILE(@@fetch_status =0) 
    BEGIN 
     IF @substr !='' SET @substr=@substr+','
     SET @substr= @substr+'sum(CASE WHEN isnull([m].MetaKey,'''')='''+ @mypathch+''''
     SET @substr= @substr +' THEN   isnull([m].MetaValue,0)  else 0 END ) AS '''+ @mypathch+''''
    FETCH FROM @d INTO  @mypathch 
    END

    CLOSE @d 
    DEALLOCATE @d    

    SET @strsql= 'SELECT [p].ProductID, [p].Name,' + @substr + ' FROM Products [p]
    inner JOIN ProductMeta [m]
    ON [p].ProductId = [m].ProductId
    GROUP BY [p].ProductID, [p].Name  
    order by  [p].ProductID'
     
    EXEC sp_executesql @strsql

    sexta-feira, 3 de julho de 2020 00:03