simple query - 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)

simple query

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-30 : 21:27:30

[code]DECLARE @tempfun TABLE
(
random VARCHAR(25),
random1 VARCHAR(15)
)
INSERT INTO @tempfun VALUES('a','b')
INSERT INTO @tempfun VALUES('c','d')

SELECT * FROM @tempfun[/code]
Result
a,b
c,d

Expected result
a,b,c,d

mu actual query will always return 2 rows of data...but could i make it into 1 row of data(duplicate column)

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-30 : 22:30:03

concatenate records without UDF
Rowset string concatenation: Which method is best ?

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

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-07-31 : 00:42:15

Hi Try this once,



DECLARE @tempfun TABLE
(
	id INT IDENTITY(1,1),
	random VARCHAR(25),
	random1 VARCHAR(25)
)
INSERT INTO @tempfun VALUES('a','b')
INSERT INTO @tempfun VALUES('c','d')

SELECT 
	STUFF(( SELECT ','+ COLUMNSS 
	FROM ( SELECT COLUMNSS  FROM @TEMPFUN 
UNPIVOT ( [COLUMNSS] FOR [COLUMNS] IN (random,random1) ) PP ) P FOR XML PATH('')),1,1,'') AS RESULT

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-31 : 02:39:27

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-31 : 02:51:34

erm....i just wanna have duplicate column without concatenation it together...so sorry..was busying other thing...
random random1 random random1
a b c d

Hope can help...but advise to wait pros with confirmation...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-31 : 03:08:58

what if you have


INSERT INTO @tempfun VALUES('a','b')
INSERT INTO @tempfun VALUES('c','d')
INSERT INTO @tempfun VALUES('e','f')
INSERT INTO @tempfun VALUES('g','h')

How do you want the result like ?

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

saran_d28
Starting Member

36 Posts

Posted - 2009-07-31 : 08:05:47

Hi Try this one,
DECLARE @tempfun TABLE
(
random VARCHAR(25),
random1 VARCHAR(15)
)
INSERT INTO @tempfun VALUES('a','b')
INSERT INTO @tempfun VALUES('c','d')

DECLARE @STR varCHaR(200)
set @str = ''
SELECT @STR = @str + ',' + RANDOM + ','+ random1 FROM @TEMPFUN
SELECT substring(@str,2, len(@str))

Check and post your results

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources