how to do - 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)

how to do

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-04-11 : 21:01:59

we have some data like below format, how we merge Cole1 values with relational ID?

see below output.

Declare @tbl1 table
(ID INT,
Col1 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A'

INSERT INTO @tbl1
SELECT 1,'B'

INSERT INTO @tbl1
SELECT 1,'C'

INSERT INTO @tbl1
SELECT 2,'X'

INSERT INTO @tbl1
SELECT 3,'Y'

INSERT INTO @tbl1
SELECT 4,'Aa'

INSERT INTO @tbl1
SELECT 4,'Ab'

output looks like this:
id col1
1 A B C
2 X
3 Y
4 Aa Ab

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-11 : 21:32:47

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

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

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-04-12 : 07:01:51

it does not help for me...

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-12 : 07:20:57

Declare @tbl1 table
(ID INT,
Col1 varchar(50)
)

INSERT INTO @tbl1
SELECT 1,'A'

INSERT INTO @tbl1
SELECT 1,'B'

INSERT INTO @tbl1
SELECT 1,'C'

INSERT INTO @tbl1
SELECT 2,'X'

INSERT INTO @tbl1
SELECT 3,'Y'

INSERT INTO @tbl1
SELECT 4,'Aa'

INSERT INTO @tbl1
SELECT 4,'Ab'

SELECT DISTINCT A.ID, STUFF((SELECT ' ' + B.[COL1] FROM @TBL1 B WHERE B.ID = A.ID FOR XML PATH('')), 1, 1, '') AS [COL 1] FROM @TBL1 A

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2012-04-12 : 07:28:31

prefect thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources