transpose

  • Hi,

    i have Table Name Country with field Country_id and Country_Name.

    Country_IDCountry_Name

    4 US

    2 UK

    3 India

    1 Argentina

    5 Belgium

    In above table how rows convert to column and reverse process

  • nithyapoy (4/7/2008)


    Hi,

    i have Table Name Country with field Country_id and Country_Name.

    Country_IDCountry_Name

    4 US

    2 UK

    3 India

    1 Argentina

    5 Belgium

    In above table how rows convert to column and reverse process

    Assuming you are on SQL Server 2000, it is a pain to do. You could use switch case statements for every column you want to create. Sort of like it is described on http://msdn2.microsoft.com/en-us/library/aa172756(SQL.80).aspx. However, since your list of columns is most likely to change (if you add a new country), you would need to build up this query (using dynamic SQL). On 2005 it can be achieved in an easier way (see http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx, but you would still need to use dynamic SQL).

    But maybe pivoting the above table is not needed at all 🙂 . Could you explain why you are trying to pivot the table you gave above.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • nithyapoy (4/7/2008)


    Hi,

    i have Table Name Country with field Country_id and Country_Name.

    Country_IDCountry_Name

    4 US

    2 UK

    3 India

    1 Argentina

    5 Belgium

    In above table how rows convert to column and reverse process

    As long as you are asured of uniqueness, and know the values that you wish to make columns out of, it is a simple pivot table and you can use most any aggregation you choose! (Well not really but sort of MAX & SUM come to mind)

    declare @tab table

    (

    Country_ID int

    ,Country_Name varchar(20)

    )

    insert into @tab

    select 4, 'US'

    union all

    select 2, 'UK'

    union all

    select 3, 'India'

    union all

    select 1, 'Argentina'

    union all

    select 5, 'Belgium'

    select * from @tab

    select

    Max(case when country_id = 4 then country_id else 0 end) as US

    ,Max(case when country_id = 2 then country_id else 0 end) as UK

    ,Max(case when country_id = 3 then country_id else 0 end) as India

    ,Max(case when country_id = 1 then country_id else 0 end) as Argentina

    ,Max(case when country_id = 5 then country_id else 0 end) as Belgium

    from @tab

    Why would you need to reverse the process? You already have that?

  • Thanks.

    But Is it possible without creating Temporary Table.

  • nithyapoy (4/8/2008)


    Thanks.

    But Is it possible without creating Temporary Table.

    You do not need the temporary table (or table variable) at all. Just take the select part of Absinthe's example, and use Country instead of @tab. Absinthe has included it to provide you with an executable example to demonstrate the results.

    Note, that as mentioned before, the query will work if your list of countries will not change. If you want to generate the table based on countries that you currently have in your Countries table, then you will need to generate the above query using dynamic SQL.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • If you want to doit based on what is in the table and have an arbitrary number of columns then I just answered that one in another thread:

    http://www.sqlservercentral.com/Forums/FindPost481696.aspx

Viewing 6 posts - 1 through 5 (of 5 total)

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