SQL Query Prob

Amit Kumar G

Guys ... Need help on urgent basis... I have two tables 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') Now i need output like Id Name Reason1 Reason2 Reason3 Reason4 1 Shon X Z K Null 2 Julie Y NULL NULL Null I can restrict upto 4 reasons.... PLEASE PLEASE Help Thanks!!

Krish KP

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