Conver row result into column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Conver row result into column

Author

Topic

shukla_dh
Starting Member

9 Posts

Posted - 2010-06-24 : 03:20:04

I want to convert my row result into column value.

Select * from Product.

Result

ID Name Category
-----------------------------
1 Pen Stationary
2 Computer Electronic
3 Pepsi Cold Drink
4
5
Etc Etc..

select Category from Product Retruns

Stationary
Electronic
Cold Drink
...
...

now i want to convert this result into column like

Station Electronic Coild Drink .... .....
------------------------------------------------

Is is possible to convert my row result into column and on the base of that i want to display further data.

Dhaval Shukla
.Net Developer

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-24 : 03:22:52

are you using SQL 2005 / 2008 ?
Check out PIVOT operator in Books OnLine

KH
[spoiler]Time is always against us[/spoiler]

shukla_dh
Starting Member

9 Posts

Posted - 2010-06-24 : 03:50:04

quote:
Originally posted by khtan

are you using SQL 2005 / 2008 ?
Check out PIVOT operator in Books OnLine

KH
[spoiler]Time is always against us[/spoiler]

I am using sql 2000

Dhaval Shukla
.Net Developer

Sachin.Nand

2937 Posts

Posted - 2010-06-24 : 04:01:35

Are your categories static?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-24 : 04:01:51

you can do something like


select col1,
       max ( case when col2 = 'Stationary' then col3 end),
       max ( case when col2 = 'Electronic' then col3 end),
       max ( case when col2 = 'Coild'      then col3 end)
from   table1
group by col1

KH
[spoiler]Time is always against us[/spoiler]

Devart
Posting Yak Master

102 Posts

Posted - 2010-06-24 : 06:36:06

Hello,

If your categories are static, you can use "CASE WHEN ... THEN ... ELSE ... END" for each category.
If your categories are not static, you can try it:

select 1 as id into ##product;

declare curs cursor read_only forward_only
for select category from product group by category

declare @category varchar(255);
declare @i int;
declare @sql varchar (255);
set @i=0;
open curs
fetch next from curs into @category
while @@fetch_status=0
begin
set @i=@i+1
set @sql = 'alter table ##product add c_'+cast(@i as varchar(10))+' varchar(255) null'
exec (@sql)
set @sql = 'update ##product set c_'+cast(@i as varchar(10))+'='+char(39)+@category+char(39)
exec (@sql)

fetch next from curs into @category
end
close curs
deallocate curs

alter table ##product drop column id

select * from ##product
drop table ##product

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

shukla_dh
Starting Member

9 Posts

Posted - 2010-06-25 : 01:42:18

quote:
Originally posted by Devart

Hello,

If your categories are static, you can use "CASE WHEN ... THEN ... ELSE ... END" for each category.
If your categories are not static, you can try it:

select 1 as id into ##product;

declare curs cursor read_only forward_only
for select category from product group by category

declare @category varchar(255);
declare @i int;
declare @sql varchar (255);
set @i=0;
open curs
fetch next from curs into @category
while @@fetch_status=0
begin
set @i=@i+1
set @sql = 'alter table ##product add c_'+cast(@i as varchar(10))+' varchar(255) null'
exec (@sql)
set @sql = 'update ##product set c_'+cast(@i as varchar(10))+'='+char(39)+@category+char(39)
exec (@sql)

fetch next from curs into @category
end
close curs
deallocate curs

alter table ##product drop column id

select * from ##product
drop table ##product

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

Thanks i got solution.

Dhaval Shukla
.Net Developer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-25 : 03:22:11

quote:
Originally posted by Devart

Hello,

If your categories are static, you can use "CASE WHEN ... THEN ... ELSE ... END" for each category.
If your categories are not static, you can try it:

select 1 as id into ##product;

declare curs cursor read_only forward_only
for select category from product group by category

declare @category varchar(255);
declare @i int;
declare @sql varchar (255);
set @i=0;
open curs
fetch next from curs into @category
while @@fetch_status=0
begin
set @i=@i+1
set @sql = 'alter table ##product add c_'+cast(@i as varchar(10))+' varchar(255) null'
exec (@sql)
set @sql = 'update ##product set c_'+cast(@i as varchar(10))+'='+char(39)+@category+char(39)
exec (@sql)

fetch next from curs into @category
end
close curs
deallocate curs

alter table ##product drop column id

select * from ##product
drop table ##product

Devart,
Tools for SQL Server
http://www.devart.com/dbforge/sql

You seem to be a cursor-lover
Here is another alternate
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources