April 22, 2013 at 2:20 am
Hi ,
Create table temp1
(Num varchar(50),
id uniqueidentifier)
create table temp2
(
num1 varchar(50),
value varchar(max)
)
----
INSERT INTO [temp1]
([Num]
,[id])
VALUES
( '22','50C6CC7C-140E-4697-9287-748AB307C497')
INSERT INTO [temp1]
([Num]
,[id])
VALUES
( '22','DB63AF5A-E8E6-42CC-AEC1-3FFA951FC8DD')
INSERT INTO [temp1]
([Num]
,[id])
VALUES
( '22','A8BA7E4D-8EAE-44A5-9172-2C0D0E618580')
INSERT INTO [temp1]
([Num]
,[id])
VALUES
( '22','50C6CC7C-140E-4697-9287-748AB307C497')
INSERT INTO [temp1]
([Num]
,[id])
VALUES
( '21','CDC4C3F3-88B5-47BB-8B24-A878A766C76E')
INSERT INTO [temp1]
([Num]
,[id])
VALUES
( '21','0B981992-55A0-4FE0-85F2-0A7E43A1A468')
INSERT INTO [temp1]
([Num]
,[id])
VALUES
( '21','0B981992-55A0-4FE0-85F2-0A7E43A1A469')
INSERT INTO [temp2]
([Num1]
,[id])
VALUES
( '21',null)
INSERT INTO [temp2]
([Num1]
,[id])
VALUES
( '22',null)
Select * from temp1
join temp2
on temp1.num=temp2.num1
I need o/p as
temp2 table
Num1 Value
22'X:50C6CC7C-140E-4697-9287-748AB307C497;X:DB63AF5A-E8E6-42CC-AEC13FFA951FC8DD;X:A8BA7E4D-8EAE-44A5-9172-2C0D0E618580;'
21'X:CDC4C3F3-88B5-47BB-8B24-A878A766C76E;X:0B981992-55A0-4FE0-85F2-0A7E43A1A468;X:0B981992-55A0-4FE0-85F2-0A7E43A1A469;
Ie,I need value field of my temp2 table should be populated with Id's of temp1 concatinated with 'X:' grouped under Num1 COlumn
Please help me in this regard
April 22, 2013 at 4:38 am
Please try below query this help you.
SELECT
num1,
STUFF((SELECT ';X: ' + cast(id AS varchar(MAX))
FROM temp1 t1 WHERE t1.num=t2.num1
FOR XML PATH('')), 1, 1, '') [id]
FROM temp2 t2
GROUP BY t2.num1
ORDER BY 1
Regards,
Arjun
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply