none
Transpose help please

    Question

  • Hi

    I have a table of sample data consisting of:

    ID    Value

    1 value1
    2 value2
    5 value3
    10 value4
    20 value5
    100 value6
    200 value7
    500 value8
    501 value9

    What I would like to do is grab the Value column and pivot this so that all of the values in the value column are displayed as one row.  I would also like the values to be displayed as per the ID order and dynamic column names to appear - ie value2 will be in column labelled '2' or 'Two'

    Any assistance on this will be grateful.

    Thanks.


    • Edited by warnerrj79 Thursday, August 01, 2013 10:41 AM Incorrect title
    Thursday, August 01, 2013 10:38 AM

All replies

  • can you pen down your expected result.

    Nothing is Permanent... even Knowledge.... <a href="http://everysolution.wordpress.com/"> </a>

    Thursday, August 01, 2013 10:46 AM
  • can you pen down your expected result.

    Nothing is Permanent... even Knowledge.... <a href="http://everysolution.wordpress.com/"> </a>

    Hi

    Here it is:

    1       2       3       4        5
    value1  value2  value3  value4   value5


    Thanks.


    • Edited by warnerrj79 Thursday, August 01, 2013 10:49 AM formatting
    Thursday, August 01, 2013 10:48 AM
  • CREATE TABLE Foo (
      foo_type CHAR(1) PRIMARY KEY,
      foo_value INT);

    INSERT INTO Foo VALUES('A', 1);
    INSERT INTO Foo VALUES('B', 2);
    INSERT INTO Foo VALUES('C', 3);
    INSERT INTO Foo VALUES('D', 4);

    -- pivot using CASE
    SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END) AS A,
            MAX(CASE WHEN foo_type = 'B' THEN foo_value END) AS B,
            MAX(CASE WHEN foo_type = 'C' THEN foo_value END) AS C,
            MAX(CASE WHEN foo_type = 'D' THEN foo_value END) AS D
    FROM Foo;

    -- dynamic pivot (SQL Server 2005/2008)
    DECLARE @pivot_cols NVARCHAR(1000);
    SELECT @pivot_cols =
            STUFF((SELECT DISTINCT '],[' + foo_type
                   FROM Foo
                   ORDER BY '],[' + foo_type
                   FOR XML PATH('')
                   ), 1, 2, '') + ']';

    DECLARE @pivot_query NVARCHAR(2000);
    SET @pivot_query =
    N'SELECT ' + @pivot_cols +
      'FROM Foo ' +
      'PIVOT ' +
      '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

    EXEC(@pivot_query);

    DROP TABLE Foo;

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, August 01, 2013 10:52 AM
    Answerer
  • Hi Uri - thanks for your reply.

    This would not work as I have only provided a sample dataset.  The values may not be static like 'A' or 'B', as detailed in SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END.  All values are different upon each previous query call.

    Thursday, August 01, 2013 11:02 AM