November 6, 2012 at 2:51 am
Hi,
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]
GO
INSERT 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 below
2001 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 records
2006 1 'Desc 6' A
2007 2 'Desc 7' B
- - - C
- - - D
- - - E
similarly for the thrid
2008 1 'Desc 8' A
2009 2 'Desc 9' B
2010 3 'Desc 10' C
- - - D
- - - E
The left outer join or the cross join does not produce my excepted result. Please suggest.
November 6, 2012 at 4:26 am
A 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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 6, 2012 at 4:47 am
How will you differentiate between first set of:
- - - D
- - - E
and the second set of
- - - D
- - - E
What makes them first or second? You need some other key as there is nothing to enforce its order...
November 6, 2012 at 5:00 am
I honestly believe that you are trying to do something wrong, but...
Here we go:
;WITH cte_ref
AS
(
SELECT R.*, ROW_NUMBER() OVER (PARTITION BY F_data_id ORDER BY F_ref_id) N
FROM [T_Lookup_Ref] R
)
, cte_set
AS
(
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_desc
FROM cte_set M
LEFT JOIN cte_ref R
ON R.N = M.N
AND R.F_data_id = M.F_data_id
ORDER BY M.N, M.F_looup_desc, R.[F_ref_id]
November 6, 2012 at 6:36 am
Many thanks for the reply 🙂
November 6, 2012 at 6:45 am
Please note, that the order of output is a bit different...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply