April 7, 2008 at 1:20 am
Hi,
i have Table Name Country with field Country_id and Country_Name.
Country_ID Country_Name
4 US
2 UK
3 India
1 Argentina
5 Belgium
In above table how rows convert to column and reverse process
April 7, 2008 at 2:19 am
nithyapoy (4/7/2008)
Hi,i have Table Name Country with field Country_id and Country_Name.
Country_ID Country_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
April 8, 2008 at 9:24 am
nithyapoy (4/7/2008)
Hi,i have Table Name Country with field Country_id and Country_Name.
Country_ID Country_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?
April 8, 2008 at 11:53 pm
Thanks.
But Is it possible without creating Temporary Table.
April 9, 2008 at 1:55 am
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
April 9, 2008 at 7:31 am
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:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply