merge multi to one field - 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

SQL Server 2005 Forums

Transact-SQL (2005)

merge multi to one field

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-06-28 : 05:41:12

SELECT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

TABLE_NAME COLUMN_NAME
CUSTOMER    CUUNUM
CUSTOMER    DIVNUM
CUSTOMER    RETNUM

i want to get one column
a.CUUNUM=b.CUUNUM AND a.DIVNUM=b.DIVNUM AND a.RETNUM=b.RETNUM

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-28 : 06:50:15

It should be helpful to you to start with:

Declare @svar varchar(max)
set @svar= ' 1=1 '

select @svar = @svar + 'and a.' + column_name +'=b.' + column_name + ' '
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where table_name = 'CUSTOMER'

print @svar

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-06-28 : 07:10:24

i get

1=1 and a.CUUNUM=b.CUUNUM and a.DIVNUM=b.DIVNUM and a.RETNUM=b.RETNUM

how to hide 1=1 and

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 07:31:42

[code]
Declare @svar varchar(max)
set @svar= ''
select @svar = @svar + 'and a.' + column_name +'=b.' + column_name + ' '
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where table_name = 'Customer'
select stuff(@svar,1,3,'')

[/code]

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

PBUH

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-28 : 07:37:34

I generally avoid removing the last "and" and that the reason i add 1=1 to the condition. Many follow the practice and many criticizes it. Its an individual perspective.

Alternate way:

Declare @svar varchar(max)
set @svar= ' '

select @svar = @svar + 'and a.' + column_name +'=b.' + column_name + ' and '
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where table_name = 'CUSTOMER'

set @svar = left(@svar,len(@svar)-4)

Print @svar

Note: The table_name should be valid otherwise you will get an error.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-06-28 : 09:03:42

thanks

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-28 : 11:38:11

You are welcome

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-28 : 23:49:32


set 	@svar= NULL
select 	@svar = isnull(@svar + 'and ', '') + 'a.' + column_name +'=b.' + column_name + ' '
FROM 	INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
where 	table_name = 'Customer'
select	@svar

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources