InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 10:55:07 GMT20http://www.sqlservercentral.com/Forums/Topic1381460-392-1.aspxPlease note, that the order of output is a bit different...Tue, 06 Nov 2012 06:45:00 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1381460-392-1.aspxMany thanks for the reply :-)Tue, 06 Nov 2012 06:36:06 GMTdoraiswamy.ghttp://www.sqlservercentral.com/Forums/Topic1381460-392-1.aspxI honestly believe that you are trying to do something wrong, but...Here we go:[code="sql"];WITH cte_refAS( SELECT R.*, ROW_NUMBER() OVER (PARTITION BY F_data_id ORDER BY F_ref_id) N FROM [T_Lookup_Ref] R), cte_setAS( SELECT S.N ,M.F_data_id ,M.F_looup_desc FROM T_Master_lookup M CROSS JOIN (SELECT DISTINCT N FROM cte_ref) S(N) )SELECT R.F_ref_id ,R.F_data_id ,R.F_ref_desc ,M.F_looup_descFROM cte_set MLEFT JOIN cte_ref RON R.N = M.N AND R.F_data_id = M.F_data_idORDER BY M.N, M.F_looup_desc, R.[F_ref_id][/code]Tue, 06 Nov 2012 05:00:43 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1381460-392-1.aspxHow will you differentiate between first set of:- - - D- - - E and the second set of - - - D- - - EWhat makes them first or second? You need some other key as there is nothing to enforce its order...Tue, 06 Nov 2012 04:47:38 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1381460-392-1.aspxA join will not work, as it cannot incorporate the logic that you need results sets in groups of 5.You can try to write a LEFT OUTER JOIN, and then inspect the result set for missing rows, but it won't be easy.Tue, 06 Nov 2012 04:26:48 GMTKoen Verbeeckhttp://www.sqlservercentral.com/Forums/Topic1381460-392-1.aspxHi,CREATE TABLE [T_Master_lookup]( [F_lookup_id] [int] NOT NULL, [F_data_id] [int] NOT NULL, [F_looup_desc] [nchar](10) NOT NULL) ON [PRIMARY]GOINSERT INTO [T_Master_lookup] ([F_lookup_id],[F_data_id],[F_looup_desc]) VALUES (1001,1,'A');INSERT INTO [T_Master_lookup] ([F_lookup_id],[F_data_id],[F_looup_desc]) VALUES (1002,2,'B');INSERT INTO [T_Master_lookup] ([F_lookup_id],[F_data_id],[F_looup_desc]) VALUES (1003,3,'C');INSERT INTO [T_Master_lookup] ([F_lookup_id],[F_data_id],[F_looup_desc]) VALUES (1004,4,'D');INSERT INTO [T_Master_lookup] ([F_lookup_id],[F_data_id],[F_looup_desc]) VALUES (1005,5,'E');CREATE TABLE [T_Lookup_Ref]( [F_ref_id] [int] NOT NULL, [F_data_id] [int] NOT NULL, [F_ref_desc] [nchar](10) NOT NULL) ON [PRIMARY]INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2001,1,'Desc 1');INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2002,2,'Desc 2'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2003,3,'Desc 3'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2004,4,'Desc 4'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2005,5,'Desc 5'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2006,1,'Desc 6'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2007,2,'Desc 7'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2008,1,'Desc 8'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2009,2,'Desc 9'); INSERT INTO [T_Lookup_Ref]([F_ref_id],[F_data_id],[F_ref_desc])VALUES (2010,3,'Desc 10');My output must be like that all the records in the T_Master_lookup must be shown like the below2001 1 'Desc 1' 'A'2002 2 'Desc 2' 'B'2003 3 'Desc 3' 'C'2004 4 'Desc 4' 'D'2005 5 'Desc 5' 'E'For the second set of records2006 1 'Desc 6' A2007 2 'Desc 7' B- - - C- - - D- - - Esimilarly for the thrid2008 1 'Desc 8' A2009 2 'Desc 9' B2010 3 'Desc 10' C- - - D- - - EThe left outer join or the cross join does not produce my excepted result. Please suggest.Tue, 06 Nov 2012 02:51:57 GMTdoraiswamy.g