quote:

---

Originally posted by sensyguy

Can't we do this in backend?

Thanks & Regards
Sensy_Guy

---

quote:

---

Originally posted by khtan

seriously . . it is much easier and efficient to do this in your front end. Doing this in SQL will required some looping or reading back the previous rows to compare etc.

Why must you do this in the query ?

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by Jeff Moden

quote:

---

Originally posted by khtan

seriously . . it is much easier and efficient to do this in your front end. Doing this in SQL will required some looping or reading back the previous rows to compare etc.

Why must you do this in the query ?

---

KH
[spoiler]Time is always against us[/spoiler]

---

No... no looping...

--Jeff Moden

---

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

---

quote:

---

Originally posted by sensyguy

Hi khtan

thanks for your reply...

kindly help me how to do this....

if it is more tricky then ill ask my ppl to do it in front end..

please help me... i need this query ... very urgent.

thanks in advance

Thanks & Regards
Sensy_Guy

---

--===== Define the tables and insert some test data
DECLARE @TableA TABLE (id INT,name VARCHAR(5),add1 VARCHAR(5),add2 VARCHAR(5))
 INSERT INTO @TableA (id,name,add1,add2)
 SELECT '1','A','xxx','yyy' UNION ALL
 SELECT '2','B','aaa','bbb' UNION ALL
 SELECT '3','C','ccc','ddd'
;
DECLARE @TableB TABLE (id INT ,exam VARCHAR(5))
 INSERT INTO @TableB
 SELECT '1','a' UNION ALL
 SELECT '2','b' UNION ALL
 SELECT '3','c' UNION ALL
 SELECT '4','d' UNION ALL
 SELECT '5','e' UNION ALL
 SELECT '6','f' UNION ALL
 SELECT '7','g'
;
DECLARE @TableC TABLE (id INT,table_aid INT,table_bid INT,total INT)
 INSERT INTO @TableC (id,table_aid,table_bid,total)
 SELECT '1','1','1','80' UNION ALL
 SELECT '2','1','2','60' UNION ALL
 SELECT '3','1','3','50' UNION ALL
 SELECT '4','1','4','40' UNION ALL
 SELECT '5','1','5','30' UNION ALL
 SELECT '6','2','1','30' UNION ALL
 SELECT '7','2','2','40' UNION ALL
 SELECT '8','2','3','50' UNION ALL
 SELECT '9','2','6','60' UNION ALL
 SELECT '10','2','7','50'
;
--===== Solve the problem
   WITH 
cteAgg AS
        (--==== Bridge table info
         SELECT Table_AID,
                Table_BID
           FROM @TableC
          UNION ALL
         --==== Orphaned TableA entries for ADD1
         SELECT a.ID AS Table_AID,
                '' AS Table_BID
           FROM @TableA a
          WHERE NOT EXISTS (SELECT 1 FROM @TableC c WHERE c.Table_AID = a.ID)
          UNION ALL
         --==== Orphaned TableA entries for ADD2
         SELECT a.ID AS Table_AID,
                '' AS Table_BID
           FROM @TableA a
          WHERE NOT EXISTS (SELECT 1 FROM @TableC c WHERE c.Table_AID = a.ID)
        )
,
cteSeq AS
(
 SELECT ROW_NUMBER() OVER (PARTITION BY Table_AID ORDER BY Table_AID, Table_BID) AS Seq,
        Table_AID,
        Table_BID
   FROM cteAgg
)                
 SELECT CASE WHEN s.Seq = 1 THEN CAST(a.ID AS VARCHAR(10)) ELSE '' END AS ID,
        CASE WHEN s.Seq = 1 THEN a.Name ELSE '' END AS Name,
        CASE WHEN s.Seq = 1 THEN a.Add1 WHEN s.Seq = 2 THEN a.Add2  ELSE '' END AS Addr,
        ISNULL(CAST(b.ID AS VARCHAR(10)),'') AS ExamID
   FROM cteSeq s
   FULL OUTER JOIN @TableA a ON s.Table_AID = a.ID
   FULL OUTER JOIN @TableB b ON s.Table_BID = b.ID

quote:

---

Originally posted by khtan This with running total falls in the group of "can and should not"

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Originally posted by Jeff Moden

quote:

---

Originally posted by khtan This with running total falls in the group of "can and should not"

---

KH
[spoiler]Time is always against us[/spoiler]

---

Any bets on the running total thing?

--Jeff Moden

---

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

---

	SELECT	[ID] 	= CONVERT(varchar(10), coalesce(a.ID, c.Table_AID)), 
		[Name]	= coalesce(a.Name, ''),
		[Addr]	= coalesce(a.Addr, ''), 
		ExamID	= coalesce(CONVERT(varchar(10), c.Table_BID), '')
	FROM
	(
		SELECT	[ID],
			[Name], 
			Addr, 	
			row_no = row_number() OVER (PARTITION BY ID ORDER BY Addr)
		FROM	@TableA a
			unpivot
			(
				Addr
				FOR AddrCol IN (Add1, Add2)
			) up
	) a
	FULL JOIN @TableC c	ON	a.ID		= c.Table_AID
				AND	a.row_no	= c.Table_BID

quote:

---

Originally posted by khtanSQL vs Front end ?
Can vs Should not ?

Burning midnight oil again ?

Put it out . . save energy and go to bed

---