Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

building the query for the below output Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 2:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:38 AM
Points: 18, Visits: 21
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.
Post #1381460
Posted Tuesday, November 6, 2012 4:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:52 PM
Points: 13,368, Visits: 11,148
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1381487
Posted Tuesday, November 6, 2012 4:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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...



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1381495
Posted Tuesday, November 6, 2012 5:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
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]




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1381501
Posted Tuesday, November 6, 2012 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 22, 2013 3:38 AM
Points: 18, Visits: 21
Many thanks for the reply
Post #1381538
Posted Tuesday, November 6, 2012 6:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:55 AM
Points: 2,873, Visits: 5,185
Please note, that the order of output is a bit different...


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1381544
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse