Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    66

    Unanswered: How to pivot this?

    Inside my SP, I'm doing:
    Code:
    CREATE STORED PROCEDURE dbo.GetTopMembers
    AS
    BEGIN
    DECLARE @table TABLE 
    (
        row_id INT IDENTITY NOT NULL,
        mem_id INT NOT NULL
    );
    
    INSERT INTO @table
    SELECT TOP(5) mem_id FROM dbo.members;
    
    SELECT mem_id FROM @output ORDER BY row_id ASC;
    END;
    
    
    Then in my main stored procedure, I'm doing:
    DECLARE @output TABLE ( row_id INT IDENTITY NOT NULL, id INT );
    INSERT INTO @output
    EXECUTE dbo.GetTopMembers;
    
    DECLARE @mem1Id INT, @mem2Id INT, @mem3Id, @mem4Id, @mem5Id;
    SELECT @mem1Id = id FROM @Output WHERE row_id = 1;
    SELECT @mem2Id = id FROM @Output WHERE row_id = 2;
    SELECT @mem3Id = id FROM @Output WHERE row_id = 3;
    SELECT @mem4Id = id FROM @Output WHERE row_id = 4;
    SELECT @mem5Id = id FROM @Output WHERE row_id = 5;
    Instead of doing the 5 select statements, is there a way to "pivot" the columns so it has column1Id, column2Id,column3Id....etc so I can just do one select statement???


    nevermind, figured it out.
    Last edited by sqlguru; 07-05-09 at 12:50.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You realise that TOP without ORDER BY is meaningless, right?

    Have you looked at the PIVOT operator?
    This coupled with Row_Number() will give you what you want.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    135
    see examples for pivot in books online

    example for pivot
    SQL Server Forums - pivot table

    try by dynamic cross tab
    Dynamic Crosstab with multiple PIVOT Columns - Madhivanan

Posting Permissions

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