With simple query is not possible. however check this
SET NOCOUNT ON
DECLARE @T1_id AS Int
DECLARE @T1_Name AS Varchar(10)
DECLARE @T2_id AS Int
DECLARE @T2_Reason AS Varchar(5)
DECLARE @qryResult AS Varchar(256)
DECLARE @cnt AS Smallint
IF NOT EXISTS(SELECT * FROM tempdb..sysobjects WHERE name LIKE '#T3[_]%')
CREATE TABLE #T3(id int, Name varchar(10), Reason1 varchar(5),
Reason2 varchar(5), Reason3 varchar(5), Reason4 varchar(5))varchar(5), Reason4 varchar(5))
DELETE FROM #T3
DECLARE CurT1 CURSOR FOR SELECT * FROM #t1
OPEN CurT1
FETCH CurT1 INTO @T1_id, @T1_Name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #T3 (id, Name) VALUES(@T1_id, @T1_Name)
SET @cnt = 0
DECLARE CurT2 CURSOR FOR SELECT \* FROM #t2 WHERE id = @T1\_id
OPEN CurT2
FETCH CurT2 INTO @T2\_id, @T2\_Reason
WHILE @@FETCH\_STATUS = 0
BEGIN
SET @cnt = @cnt + 1
SET @qryResult = 'UPDATE #T3 SET Reason' + Cast(@cnt AS Varchar(10)) + ' = '''
+ @T2\_Reason + ''' WHERE id = ' + Cast(@T1\_id As Varchar(5))
EXEC (@qryResult)
FETCH CurT2 INTO @T2\_id, @T2\_Reason
END
CLOSE CurT2
DEALLOCATE CurT2
FETCH CurT1 INTO @T1\_id, @T1\_Name
END
CLOSE CurT1
DEALLOCATE CurT1
SET NOCOUNT OFF
SELECT * FROM #T3
--SELECT * FROM #T1
--SELECT * FROM #T2
/*
CREATE TABLE #T1(id int,Name varchar(10))
insert into #T1 (id,Name) values (1,'Shon')
insert into #T1 (id,Name) values (2,'Julie')
CREATE TABLE #T2(id int,Reason varchar(5))
insert into #T2 (id,Reason) values (1,'X')
insert into #T2 (id,Reason) values (2,'Y')
insert into #T2 (id,Reason) values (1,'Z')
insert into #T2 (id,Reason) values (1,'K')
*/
Regards KP
