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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy