T-SQL Challenge #1

Question

T-SQL Challenge #1

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Ashish Gilhotra SSC Enthusiast Points: 100 More actions · Answer 1

Ohk Thanks for acknowledge this,,, i write this to solve in SQL 2000 and only for that same data i generate row numbers in this so i write this if you some solution which works on all the data then what i write..

-- Code for Challenge #1

SELECT T1.code, T1.aname, D.bname, D.cname

FROM TableA T1

LEFT JOIN

(

SELECT COALESCE(D2.code, D3.code) AS code

,D2.bname

,D3.cname

FROM

(

SELECT ROW_NUMBER() OVER (PARTITION BY T2.code ORDER BY T2.bname) AS RowID

,T2.*

FROM TableB T2

) D2

FULL JOIN

(

SELECT ROW_NUMBER() OVER (PARTITION BY T3.code ORDER BY T3.cname) AS RowID

,T3.*

FROM TableC T3

) D3

ON D2.code = D3.code

AND D2.RowId = D3.RowId

) D

ON T1.code = D.code

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 2

Sharon Matyk (9/14/2009)
Whilst I enjoyed the mental exercise and learning something I didn't know about 2005, I just can't picture when I would actually do such a join in real life. Is there a logical, meaningful example that was the basis of this problem ... and if not, does anyone have any ideas of when you'd prefer results which only join multiple values in this manner?

Heh... I don't know about now, but I went through that with customer placement with AT&T years ago... even though a 3.5" diskette could hold thousands of new customer records, they only allowed us to put 100 on a disk and they had to all be in the same "region". Even when they shifted to FTP uploads, they still kept the paradigm of only 100 customers in a batch.

The reason why they did this is they would have a keypunch person verify each phone number manually then prep a work order for a tech. They used 100 as the breaking point for how many a tech should be able to handle in a day.

I think they're a little more efficient now... well... maybe. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AlexT-359331 SSC Veteran Points: 219 More actions · Answer 3

WITH dd(code, aname, bname, ROWNUMBER) as

(

select a.code, a.aname, b.bname, ROW_NUMBER() OVER(PARTITION BY a.code order BY a.code )

from @a as a

left outer join @b-2 as b on a.code=b.code

),

ddd(code, cname, ROWNUMBER) as

(

select c.code, c.cname, ROW_NUMBER() OVER(PARTITION BY code order BY code )

from @C as c

)

select COALESCE(d.code, d1.code) as code, COALESCE(aname,(select top 1 a.aname from @a a where a.code=code)) as aname, d.bname, d1.cname

from dd as d

full join ddd as d1 on d.code=d1.code and d.ROWNUMBER=d1.ROWNUMBER;

with d(name,code,ROW)

as(

SELECT bname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row

FROM @b-2 b

),

dd(name,code,ROW) as

(SELECT cname,code,ROW_NUMBER() OVER(PARTITION BY code order by code) AS row

FROM @C

)

SELECT a.* , b.name1, b.name2

FROM @a a

LEFT JOIN (

select d.code as code1, d.name as name1, dd.code as code2, dd.name as name2

from d

FULL JOIN dd ON d.code = dd.code AND d.row = dd.row ) b

ON a.code IN (b.code1, b.code2)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Ouch! I just realized that I triple-posted the other day when I was having a lot of trouble with the site... :w00t:

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Vamsee Krishna A Grasshopper Points: 15 More actions · Answer 5

First we need to full join the tables @b-2 and @C based on the code and the position of the bname and cname. Then later make right join with @a table.

select a1.code,a1.aname,b1.bname,c1.cname

from @C c1

full join @b-2 b1

on c1.code=b1.code and

(select count(c2.code) from @C c2 where c1.cname>=c2.cname and c1.code=c2.code)

=

(select count(b2.code) from @b-2 b2 where b1.bname>=b2.bname and b1.code=b2.code)

right join @a a1 on isnull(b1.code,c1.code)= a1.code

guruvn-335273 Valued Member Points: 68 More actions · Answer 6

select

a.code

, aname

, bname

, cname

from (

select

code

, bName

, ROW_NUMBER() over (partition by code order by code) as br

from @b-2

) b

right join (

select

code

, cname

, ROW_NUMBER() over (partition by code order by code) as cr

from @C

) c on b.code = c.code and b.br = c.cr

right join @a a on c.code = a.code

below86 SSChampion Points: 11513 More actions · Answer 7

Here is what I came up with:

SELECT ISNULL(ac.code, b1.code) AS code, a2.aname, b1.bname, ac.cname

FROM (SELECT a.code, a.aname, c.cname, ROW_NUMBER() OVER (Partition BY a.aname ORDER BY a.code, a.aname) AS RNO

FROM @a AS a

LEFT JOIN @C AS c

ON a.code = c.code) AS ac

FULL JOIN (SELECT b.code, b.bname, ROW_NUMBER() OVER (Partition BY b.code ORDER BY b.code, b.bname) AS RNO

FROM @b-2 b) AS b1

ON ac.code = b1.code

AND ac.RNO = b1.RNO

LEFT JOIN @a AS a2

ON ISNULL(ac.code, b1.code) = a2.code

ORDER BY ISNULL(ac.code, b1.code), a2.aname, b1.bname, ac.cname

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

pallu2201 Newbie Points: 1 More actions · Answer 8

select a.*,d.bname,d.cname from @a a

left join

(select b.code b_code,b.bname,c.code c_code,c.cname from

(select ROW_NUMBER() over(partition by code order by code)Rank

,* from @b-2)b full join

(select ROW_NUMBER() over(partition by code order by code)Rank

,* from @C)c

on b.code=c.code and b.rank=c.rank)d

on a.code in (b_code,c_code)