Create Output Columns At Runtime For Distinct Valu

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 2008 Forums

Transact-SQL (2008)

Create Output Columns At Runtime For Distinct Valu

Author

Topic

Jawad
Starting Member

17 Posts

Posted - 2011-12-19 : 10:13:45

Hi

SQL Server 2008

I want to create output columns at runtime for distinct values of column Cal_2


create table T1
(col_1 smallint,
Col_2 varchar(10),
Col_3 varchar(10)
);

insert into T1 values (1,'a','abc');
insert into T1 values (1,'a','pqr');
insert into T1 values (1,'a','xyz');
insert into T1 values (2,'b','lmn');
insert into T1 values (2,'b','efg');
insert into T1 values (1,'b','hij');
insert into T1 values (1,'c','stu');


select * from T1

Col_1   Col_2   Col_3
1	a	abc
1	a	pqr
1	a	xyz
2	b	lmn
2	b	efg
1	b	hij
1	c	stu

By any procedure or query I wish to got following out put



Col_1	a	b	c
1	abc		
1	pqr		
1	xyz		
1		hij	
1			stu
2	b	lmn	
2	b	efg

Or Ideally


Col_1	a	        b	  c
1	abc,pqr,xyz	hij	  stu
2		        lmn,efg

Wishes
J a w a d

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 11:01:26

[code]
create table #T1
(col_1 smallint,
Col_2 varchar(10),
Col_3 varchar(10)
);

insert into #T1 values (1,'a','abc');
insert into #T1 values (1,'a','pqr');
insert into #T1 values (1,'a','xyz');
insert into #T1 values (2,'b','lmn');
insert into #T1 values (2,'b','efg');
insert into #T1 values (1,'b','hij');
insert into #T1 values (1,'c','stu');

;with cte
as
(
select col_1,Col_2,
stuff((select ',' + Col_3 from #T1 where col_1= t1.col_1 and Col_2 = t1.Col_2 for xml path('')),1,1,'') as col3list
from (select distinct col_1,Col_2 from #T1) t1
)
select *
from cte
pivot(max(col3list) for col_2 in (a,b,c))p
drop table #T1

output
-----------------------------------------------
col_1 a b c
-----------------------------------------------
1 abc,pqr,xyz hij stu
2 NULL lmn,efg NULL

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jawad
Starting Member

17 Posts

Posted - 2011-12-19 : 22:31:40

Hi visakh16

thanks, That is very close to what i need, buy i dont want to pass pivot values , i wish it always cater new values as well, dont want to hard code pivot like like a,b,c.
Please help me out.

Wishes
J a w a d

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-20 : 00:05:37

quote:
Originally posted by Jawad

Hi visakh16

thanks, That is very close to what i need, buy i dont want to pass pivot values , i wish it always cater new values as well, dont want to hard code pivot like like a,b,c.
Please help me out.

Wishes
J a w a d

then just extend it to use dynamic sql

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources