row merge - 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)

row merge

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-11-08 : 07:02:35

hi,

tableA:

pkid caseid userid
1 1 100
2 1 200
tableB:
pkid caseid userid casename
1 1 100 abc

ill have to get the caseid based on the userid from the tableb and then join this caseid of Table B with the case id of TableA.if i do so ill get something like

caseid casename userid
1 abc 100
1 abc 200

am i right if i left join it?

but actaully i require output
i want to merge this as like

caseid casename userid
1 abc 100;200
how to achive this.

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 07:19:08

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

PBUH

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-11-08 : 07:34:43

quote:
Originally posted by Sachin.Nand

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

PBUH

please send meworking sql for this.

Sachin.Nand

2937 Posts

Posted - 2010-11-08 : 07:44:28

[code]
DECLARE @MyData TABLE
(
OrderID INT NOT NULL,
OrderItem INT NOT NULL,
PersonaliseItem INT NOT NULL,
PersonaliseID INT NOT NULL,
OrderValue VARCHAR(100) NULL
)

INSERT INTO @MyData

SELECT 10372, 1, 1, 10116, 'xxx' UNION ALL
SELECT 10372, 3, 9, 10031, 'Value1' UNION ALL
SELECT 10372, 3, 9, 10032, 'Value2' UNION ALL
SELECT 10372, 3, 10, 10037, 'Value3' UNION ALL
SELECT 10372, 3, 10, 10038, NULL UNION ALL
SELECT 10372, 3, 12, 10059, NULL UNION ALL
SELECT 10372, 3, 12, 10064, 'Value4'

SELECT *
FROM @MyData

SELECT OrderID, OrderItem,
(SELECT OrderValue + ','
FROM @MyData xm where xm.OrderID = a.OrderID and xm.OrderItem = a.OrderItem
FOR XML PATH ('')) as val
FROM @MyData a
GROUP BY OrderItem, OrderID

[/code]

PBUH

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-11-08 : 08:23:06

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources