How can we add single column value

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

How can we add single column value

Author

Topic

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-11-24 : 09:22:53

Hi,

I want to add single column value as single string from a table.

e.g.

name
----
malay
Kausik
Remma

results
------
malay, Kausik, Remma

malay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:27:06

[code]
DECLARE @List varchar(8000)

SELECT @List=COALESCE(@List+',','') + name
FROM Table

SELECT @List
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:29:53

and if you want this concatenation to be done based on unique value of another field use this


SELECT t.OtherField,LEFT(nl.NameList,LEN(nl.NameList)-1)
FROM (SELECT DISTINCT OtherField FROM Table)t
CROSS APPLY (SELECT name + ','
             FROM Table
             WHERE Otherfield=t.Otherfield
             FOR XML PATH(''))nl(NameList)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 09:32:56

and for sql 2000 solution see below

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114993

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-27 : 00:54:39

SELECT STUFF((SELECT DISTINCT ',' + CAST(yourcolumn AS VARCHAR(255))
FROM tablename
FOR XML PATH('')), 1, 1, '')
Try this

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-27 : 02:32:19

quote:
Originally posted by bklr

SELECT STUFF((SELECT DISTINCT ',' + CAST(yourcolumn AS VARCHAR(255))
FROM tablename
FOR XML PATH('')), 1, 1, '')
Try this

This will work from SQL Server 2005 version onwards

Madhivanan

Failing to plan is Planning to fail

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-05 : 05:34:32

Hi,
I need to do the opposite.

The data is currently:

M001111, M001222, M001333

and i need it to display as

M001111
M001222
M001333

How can I achieve this?

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 05:38:20

See fnParseList here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-05 : 05:51:03

r try like this


declare @str1 varchar(max)
set @str1= 'M001111,M001222,M001333'

SELECT		
		replace(SUBSTRING(@str1,charindex(',',@str1,v.number),abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))),',','')as value
FROM			master..spt_values AS v 
WHERE v.Type = 'P'
			AND v.number > 0
			AND v.number <= len(@str1)
			AND substring(',' + @str1, v.number, 1) = ','

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-05 : 06:00:12

Thanks for the feedback.
I've created the function, but how do I then use it to extract the data in the way I need it?

Do you have an example script?

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 06:03:29

[code]DECLARE @Sample VARCHAR(200)

SET @Sample = 'M001111, M001222, M001333'

SELECT LTRIM(Data)
FROM dbo.fnParseList(',', @Sample)[/code]

E 12°55'05.63"
N 56°04'39.26"

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-05 : 06:15:40

That's great thanks!....

Just one more question. The example of 'M001111, M001222, M001333' which i gave is the contents of a field (loan.policies). There may be 2 or 3 policies as it is in the case of the example for more than one record in the loan table.

I need to be able to specify in the sql script that whenever there are more than 1 policies showing in the field for any number of records, for it to remove the commas and put the data in columns.

Can this be done, instead of hard coding the contents of the field as we have done so far?

Cheers once again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 06:17:02

Also in SQL Server 2000? Or SQL Server 2005 only?

E 12°55'05.63"
N 56°04'39.26"

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-05 : 06:18:26

SQL Server 2005 mate...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 06:24:39

[code]DECLARE @Sample TABLE
(
ID INT IDENTITY(1, 1),
DATA VARCHAR(200)
)

INSERT @Sample
SELECT 'M001111, M001222, M001333'

SELECT ID,
LTRIM(f.DATA) AS Data
FROM @Sample
CROSS APPLY dbo.fnParseList(',', DATA) AS f[/code]

E 12°55'05.63"
N 56°04'39.26"

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-05 : 06:38:19

Cheers mate, that's excellent

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-05 : 08:39:54

Please ignore the following message......I've cracked it....

Thanks for all your help, it is much appreciated Peso!....

I'm now using the following version of the script which you kindly sent me which works nicely:

DECLARE @Sample TABLE
(
ID INT IDENTITY(1, 1),
DATA VARCHAR(200)
)

INSERT @Sample
SELECT policies--'M001111, M001222, M001333'
from loan
SELECT ID,
LTRIM(f.DATA) AS Data
FROM @Sample
CROSS APPLY dbo.fnParseList(',', DATA) AS f

I need to also have displayed in the results, another field from the loan table which is called ref_num, but I've tried to add it in but to no success.
Could you please advise?

Many Thanks

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-05 : 11:04:56

I tried to create the following as a view but couldn't due to the DECLARE command. Is there a way to create the following as a table (temporary or permanent) so I can link it to other tables fairly easily for use in other scripts?

DECLARE @Sample TABLE
(
ID INT IDENTITY(1, 1),
DATA VARCHAR(200),
ref_number varchar (50)
)

INSERT @Sample
SELECT policies, ref_num
from loan
SELECT ID, ref_number,
LTRIM(f.DATA) AS Data
FROM @Sample
CROSS APPLY dbo.fnParseList(',', DATA) AS f

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 12:08:35

just do like this


SELECT m.policies,l.ref_num 
FROM
(
SELECT	policies--'M001111, M001222, M001333'
from loan
SELECT	 ID,
LTRIM(f.DATA) AS Data
FROM	 @Sample
CROSS APPLY	dbo.fnParseList(',', DATA) AS f
)m
JOIN loan l
ON.....--condition here

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-02-05 : 12:09:08

Create a table-valued function. Something like:


CREATE FUNCTION dbo.udf_LoanPolicies()
RETURNS @lp TABLE 
(
	ID int identity(1,1),
	DATA	varchar(200),
	ref_number	varchar(50)
)
AS
BEGIN
	INSERT @lp
	SELECT		LTRIM(f.ListValue) AS Data,
			ref_number
	FROM		loan a
	CROSS APPLY	dbo.fnParseList(',', a.policies) AS f
   RETURN
END
GO

SELECT * FROM dbo.udf_LoanPolicies()

ams006
Yak Posting Veteran

55 Posts

Posted - 2009-02-06 : 04:50:14

Thank you very much all, I shall try these solutions out.

Kind Regards

ams006

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources