Post reply

table to table: rows to cols

  • November 16, 2005 at 7:48 am

    #89841

    Hi,

    I have a table (test) like:

    object ItemId Item    (cols)

    t1         1       aa      (row1)

    t1         2       bb      (row2)

    t1         3       cc      (row3)

    t2         3       cc      (row4)

    t2         2       bb      (row5)

    t2         1       aa      (row6)

    I would like to create a table from the previous one in the following way:

    new table:

    object   1     2     3       (these are cols)

      t1      aa   bb    cc       (row1)

      t2      zz   hh     vv      (row2)

    I mean:

    every row is object col, in this case: 2 rows t1 e t2,

    and for cols the itemID, in this case 1,2,3 and their values

    How can I reach this goal?

    Any help will be very appreciated

    Thank

  • November 16, 2005 at 7:50 am

    #604205

    http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 16, 2005 at 11:09 am

    #604250

    Try:

    SELECT TOP 100 PERCENT

    object,

    MIN(CASE WHEN itemid = 1 THEN item END) AS [1],

    MIN(CASE WHEN itemid = 2 THEN item END) AS [2],

    MIN(CASE WHEN itemid = 3 THEN item END) AS [3]

    FROM dbo.table

    GROUP BY object

    ORDER BY object

    The Min is just a device to provide a function acceptable summary views.

    It could be a 'Sum' if the column item was numeric and you required the sum of like rows.

  • November 16, 2005 at 2:25 pm

    #604295

    interesting both solution, I' ll try both

    thank

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply