Converting Rows into cloumn without concate.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Converting Rows into cloumn without concate.

Author

Topic

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-22 : 23:04:51

Hai,
i need to convert the rows into columns.but i dont want to concatenate everything into one column, i want to make as multiple column how can i do it.
declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

select * from @Customer

i want something like this

1 mobile tv comp laptop
2 mobile tv null null
3 mobile null null laptop..

is it possible to get this type of result..how?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-23 : 00:01:55

Search for Pivot or Cross tab. There are millions of post regarding this or Wait for visakh's or peso for the answer.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 05:49:06

try this one
select cid,max(case when item ='mobile' then 'mobile' else null end) as mobile,
max(case when item ='TV' then 'TV' else null end) as TV,
max(case when item ='Comp' then 'Comp' else null end) as Comp,
max(case when item ='Laptop' then 'Laptop' else null end) as Laptop
from @customer
group by cid

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-23 : 06:23:27

Try this also

declare @Customer table
(cid int,
item varchar(50))
insert into @Customer values (1,'mobile')
insert into @Customer values (1,'TV')
insert into @Customer values(1,'Comp')
insert into @Customer values(1,'Laptop')
insert into @Customer values(2,'mobile')
insert into @Customer values(2,'TV')
insert into @Customer values(3,'mobile')
insert into @Customer values(3,'Laptop')

SELECT cid, [mobile],[TV], [Comp],[Laptop] from
(select cid, item from @Customer) st
pivot
(count(item) for item in([mobile],[TV], [Comp],[Laptop]))PT

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-23 : 06:26:37

slight modification to aprichard,
SELECT cid, [mobile],[TV], [Comp],[Laptop] from
(select cid, item from @Customer) st
pivot
(max(item) for item in([mobile],[TV], [Comp],[Laptop]))PT

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-23 : 22:59:22

Hai Thanks for the reply. is there any way, that we do it make it as dynamic. B'coz items tv, laptop, computere are from config table.and at any time, new item will be added. if the need item is added i dont want to modify the query..
B'coz there will be lot of changes if i want to change the query..
that's y i asking for the solution.

maeenul
Starting Member

20 Posts

Posted - 2009-04-24 : 00:35:55

In that case you will have to create a dynamic sql. loop through the config table using a cursor and create the desired sql string in the same format as aprichard mentioned. then use exec @dynsql. So far I have not found any better way for doing this type of transpose.

-----------------------
maeenul
http://www.programmingsolution.net
http://sqlservertipsntricks.blogspot.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-25 : 03:20:54

quote:
Originally posted by Sarakumar

Hai Thanks for the reply. is there any way, that we do it make it as dynamic. B'coz items tv, laptop, computere are from config table.and at any time, new item will be added. if the need item is added i dont want to modify the query..
B'coz there will be lot of changes if i want to change the query..
that's y i asking for the solution.

Refer this
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Sarakumar
Posting Yak Master

108 Posts

Posted - 2009-04-27 : 22:17:23

Hai,
Thanks for the Reply. it works for me.Thanks for the Greathelp. Thanks for madhivanan for the dynamic query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 02:24:03

quote:
Originally posted by Sarakumar

Hai,
Thanks for the Reply. it works for me.Thanks for the Greathelp. Thanks for madhivanan for the dynamic query.

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources