Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query by joining lookup and onetomany table
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nbalraj
Starting Member

21 Posts

Posted - 01/22/2014 :  11:34:34  Show Profile  Send nbalraj a Yahoo! Message  Reply with Quote
Hi,

Can you please help with the below scenario:

opp table (primary table)
oppid opp title
1 test opp
2 test opp2
3 test opp3


bunit table (one to many table)
oppid bunitid
1 1
1 2
1 4
2 5
2 1
3 3
3 4

dunit table (one to many table)
oppid dunitid
1 1
1 2
2 1
3 3
3 1


lookup table
codeid classification description
1 bunit GED
2 bunit GLC
3 bunit CCC
4 bunit DDD
5 bunit SSS
1 dunit iii
2 dunit kkk
3 dunit nnn


Final output, we need

oppid title bunit dunit
1 test opp GED,GLC,DDD iii,kkk
2 test opp2 SSS,GED iii,nnn
3 test opp3 CCC,DDD nnn,iii


Thanks for any help and directions.

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/22/2014 :  14:57:01  Show Profile  Reply with Quote
It took me a while to figure out the ER, but I think this query works:



with opp(oppid, opp_title) as (
	select * from (values
		(1, 'test opp'),
		(2, 'test opp2'),
		(3, 'test opp3')
		) opp(oppid, opp_title)
	),

	bunit (oppid, bunitid) as (
	select * from (values
		(1, 1),
		(1, 2),
		(1, 4),
		(2, 5),
		(2, 1),
		(3, 3),
		(3, 4)
		) bunit (oppid, bunitid)
	),
	
	dunit(oppid, dunitid) as (
		select * from (values
			(1, 1),
			(1, 2),
			(2, 1),
			(3, 3),
			(3, 1)
			) dunit(oppid, dunitid)
	),
			
	lookup_table(codeid, class, descr) as (
		select * from (values
			(1, 'bunit', 'GED'),
			(2, 'bunit', 'GLC'),
			(3, 'bunit', 'CCC'),
			(4, 'bunit', 'DDD'),
			(5, 'bunit', 'SSS'),
			(1, 'dunit', 'iii'),
			(2, 'dunit', 'kkk'),
			(3, 'dunit', 'nnn')
			) lookup_table(codeid, class, descr) 
	),
	
	codes (oppid, descr) as (
		select b.oppid oppid, lt.descr from bunit b
		join lookup_table lt on b.bunitid = lt.codeid and lt.class = 'bunit'
		
		union
		
		select d.oppid, lt.descr from dunit d
		join lookup_table lt on d.dunitid = lt.codeid and lt.class = 'dunit'
	)
	
	
select opp.oppid, opp.opp_title, left(codes.descr, LEN(codes.descr) -1) as 'bunit dunit'
from opp
cross apply (
	select codes.descr + ','
	from codes
	where codes.oppid = opp.oppid
	for XML path('')
	) codes(descr)
	


I get these results:


oppid       opp_title bunit dunit
----------- --------- -----------
1           test opp  DDD,GED,GLC,iii,kkk
2           test opp2 GED,iii,SSS
3           test opp3 CCC,DDD,iii,nnn


Edited by - gbritton on 01/22/2014 15:01:01
Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 01/23/2014 :  07:22:49  Show Profile  Reply with Quote
;with cte (oppid,bunitid,description)
as
(
select b.oppid,b.bunitid,l.description from @bunit as b
inner join @lookup as l
on b.bunitid = l.codeid
where l.classification = 'bunit'
),cte1(oppid,dunitid,description)
as
(
select d.oppid,d.dunitid,l.description from @dunit as d
inner join @lookup as l
on d.dunitid = l.codeid
where l.classification = 'dunit'
)
select distinct
op.opp
, op.oppid
, op.title
, left(cte.description, LEN(cte.description) -0) as 'bunit'
, left(cte1.description, LEN(cte1.description) -0) as 'dunit'
from @opp as op
cross apply
(select ','+ c.description
from cte as c
where c.oppid = op.oppid
for XML path('')
)cte(description)
cross apply
(select ','+c.description
from cte1 as c
where c.oppid = op.oppid
for XML path('')
)cte1(description)


Veera

Edited by - VeeranjaneyuluAnnapureddy on 01/23/2014 07:46:30
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2014 :  07:23:26  Show Profile  Reply with Quote
just use a single cte instead of nested ones

;With CTE
AS
(
SELECT o.oppid, o.opptitle,b.bunitid,lb.description  as [bunit],d.dunitid,ld.description as [dunit]
FROM opp o
LEFT JOIN bunit b
ON b.oppid = o.oppid 
LEFT JOIN lookup lb
ON lb.codeid = b.bunitid
AND lb.classification = 'bunit'
LEFT JOIN dunit d
ON d.oppid = o.oppid 
LEFT JOIN lookup ld
ON ld.codeid = d.dunitid
AND ld.classification = 'dunit'
)

SELECT oppid,
opptitle,
STUFF((SELECT ',' + bunit
       FROM CTE
       WHERE oppid = c.oppid
       AND bunit IS NOT NULL
       ORDER BY bunitid
       FOR XML PATH('')),1,1,'') AS bunit,
 STUFF((SELECT ',' + dunit
       FROM CTE
       WHERE oppid = c.oppid
       AND dunit IS NOT NULL
       ORDER BY dunitid
       FOR XML PATH('')),1,1,'') AS dunit
FROM (SELECT DISTINCT oppid,opptitle FROM CTE) c



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 01/23/2014 07:24:02
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 01/23/2014 :  08:52:57  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

just use a single cte instead of nested ones

...snip





that doesn't work correctly, I think. When I run your version I get:


oppid       opp_title bunit                  dunit
----------- --------- ---------------------- ------------------------
1           test opp  GED,GED,GLC,GLC,DDD,DDD iii,iii,iii,kkk,kkk,kkk
2           test opp2 GED,SSS                iii,iii
3           test opp3 CCC,CCC,DDD,DDD     


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 01/23/2014 :  13:42:09  Show Profile  Reply with Quote
that can be dealt with by a simple tweak
see below

declare @opp table 
(
oppid int,
opp varchar(30),
title varchar(10)
)
insert @opp
values(1,'test','opp'),
(2,'test','opp2'),
(3,'test','opp3')


declare @bunit table 

(
oppid int,
bunitid int
)
insert @bunit
values(1,	1),
(1,	2),
(1,	4),
(2,	5),
(2,	1),
(3,	3),
(3,	4)

declare @dunit table 
(
oppid int,
 dunitid int
)
insert @dunit
values(1,	1),
(1,	2),
(2,	1),
(3,	3),
(3,	1)


declare @lookup table
(
codeid int,
classification varchar(50),
[description] varchar(50)
)
insert @lookup
values(1,'bunit','GED'),
(2,'bunit','GLC'),
(3,'bunit','CCC'),
(4,'bunit','DDD'),
(5,'bunit','SSS'),
(1,'dunit','iii'),
(2,'dunit','kkk'),
(3,'dunit','nnn')


;With CTE
AS
(
SELECT o.oppid, o.opp,title,b.bunitid,lb.description  as [bunit],d.dunitid,ld.description as [dunit]
FROM @opp o
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY oppid ORDER BY bunitid) AS Rn,*
           FROM @bunit) b
ON b.oppid = o.oppid 
LEFT JOIN @lookup lb
ON lb.codeid = b.bunitid
AND lb.classification = 'bunit'
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY oppid ORDER BY dunitid) AS Rn,*
           FROM @dunit) d
ON d.oppid = o.oppid 
AND d.Rn = b.Rn
LEFT JOIN @lookup ld
ON ld.codeid = d.dunitid
AND ld.classification = 'dunit'
)

SELECT oppid,
opp,title,
STUFF((SELECT  ',' + bunit
       FROM CTE
       WHERE oppid = c.oppid
       AND bunit IS NOT NULL
       ORDER BY bunitid
       FOR XML PATH('')),1,1,'') AS bunit,
 STUFF((SELECT  ',' + dunit
       FROM CTE
       WHERE oppid = c.oppid
       AND dunit IS NOT NULL
       ORDER BY dunitid
       FOR XML PATH('')),1,1,'') AS dunit
FROM (SELECT DISTINCT oppid,opp,title FROM CTE) c


output
-------------------------------------------------------
oppid	opp	title	bunit	        dunit
-------------------------------------------------------
1	test	opp	GED,GLC,DDD	iii,kkk
2	test	opp2	GED,SSS	        iii
3	test	opp3	CCC,DDD	        iii,nnn


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000