beyondrelational.com



TSQL Challenge 1

I would like to invite my readers to participate in a TSQL Challenge. And if it works well, I will come up with more such challenges and we will have more fun solving TSQL problems. I will be sharing some of the interesting TSQL challenges that I see around and we will see different ways to solve them.

Here is the first TSQL Challenge:

I found the following question in one of the SQL Server forums, a few weeks back.

There are three tables with the following data.

Table 1

code        aname
----------- ----------
1           Cat
2           Dog
3           Bird

Table 2

code        bname
----------- ----------
1           aaa
1           bbb
2           ccc
2           ddd

Table 3

code        cname
----------- ----------
1           xxx
1           yyy
1           zzz
2           www

Required Output

code        aname      bname      cname
----------- ---------- ---------- ----------
1           Cat        aaa        xxx
1           Cat        bbb        yyy
1           Cat        NULL       zzz
2           Dog        ccc        www
2           Dog        ddd        NULL
3           Bird       NULL       NULL

The query should work in SQL Server 2000 as well as SQL Server 2005/2008. However, two separate version of the query is acceptable for SQL Server 2000 and 2005/2008 (SQL Server 2005/8 has some new functions that makes writing this query easier and you can make use of them)

Please send your answers latest by 5th March 2009. I will announce the winner on 9th March 2009. I look forward to see your queries :-)

Here are the insert scripts for the sample data.

DECLARE @a TABLE (code INT, aname VARCHAR(10))
INSERT INTO @a(code, aname) SELECT 1,'Cat'
INSERT INTO @a(code, aname) SELECT 2,'Dog'
INSERT INTO @a(code, aname) SELECT 3,'Bird'

DECLARE @b TABLE (code INT, bname VARCHAR(10))
INSERT INTO @b(code, bname) SELECT 1,'aaa'
INSERT INTO @b(code, bname) SELECT 1,'bbb'
INSERT INTO @b(code, bname) SELECT 2,'ccc'
INSERT INTO @b(code, bname) SELECT 2,'ddd'

DECLARE @c TABLE (code INT, cname VARCHAR(10))
INSERT INTO @c(code, cname) SELECT 1,'xxx'
INSERT INTO @c(code, cname) SELECT 1,'yyy'
INSERT INTO @c(code, cname) SELECT 1,'zzz'
INSERT INTO @c(code, cname) SELECT 2,'www'


Comments

# re: TSQL Challenge 1

Friday, August 14, 2009 1:40 PM by sauraviit@gmail.com

--Easiest Possible Sol

with stagedata1 as(

select COUNT(*) as count,code from @a a group by a.code

union

select COUNT(*) as count,code from @b b group by b.code

union

select COUNT(*) as count,code from @c c group by c.code),

stagedata2 as(

select cmt = ROW_NUMBER() over (partition by code order by code ),code,bname

from @b

),

stagedata3 as

(

select cmt = ROW_NUMBER() over (partition by code order by code ),code,cname

from @c

)

select sd1.code,a.aname,sd2.bname,sd3.cname

from stagedata1 sd1

join @a a on sd1.code = a.code

left outer join stagedata2  sd2 on sd1.count = sd2.cmt              

                   and sd1.code = sd2.code

left outer join stagedata3  sd3 on sd1.count = sd3.cmt              

                   and sd1.code = sd3.code

# re: TSQL Challenge 1

Thursday, December 03, 2009 4:07 AM by oswalmitesh

SELECT Aname,Bname,cname

FROM

(

SELECT

B.Bname,C.Cname,COALESCE(B.Code,C.Code) AS Code,C.ROW

FROM

(

SELECT

C.Code,C.Cname,ROW_NUMBER() OVER(PARTITIOn BY C.code ORDER By C.code) ROW

FROM

@C C

)C

LEFT JOIN

(

SELECT

B.Code,B.Bname,ROW_NUMBER() OVER(PARTITIOn BY B.code ORDER By B.code) ROW

FROM

@B B

) B

ON

B.Code = C.Code AND

B.ROW =  C.ROW

UNION

SELECT

B.Bname,C.Cname,COALESCE(B.Code,C.Code)Code,B.ROW

FROM

(

SELECT

C.Code,C.Cname,ROW_NUMBER() OVER(PARTITIOn BY C.code ORDER By C.code) ROW

FROM

@C C

)C

RIGHT JOIN

(

SELECT

B.Code,B.Bname,ROW_NUMBER() OVER(PARTITIOn BY B.code ORDER By B.code) ROW

FROM

@B B

) B

ON

B.Code = C.Code AND

B.ROW =  C.ROW

)B

RIGHT JOIN

@a A

ON

A.Code = B.Code

ORDER BY

A.code,ROW

# re: TSQL Challenge 1

Thursday, February 11, 2010 11:23 PM by uzairahm

SELECT A.code,aname,bname,cname

FROM  

( SELECT B.Code,B.Bname,ROW_NUMBER() OVER(PARTITIOn BY B.code ORDER By B.code) ROW

FROM   @B B

) B

--ON     A.code = B.ROW

FULL OUTER JOIN

(

SELECT C.Code,C.Cname,ROW_NUMBER() OVER(PARTITIOn BY C.code ORDER By C.code) ROW

FROM   @C C

)C

ON     B.ROW = C.ROW

AND    B.CODE = C.code

RIGHT JOIN

  @a A

ON     A.CODE = C.code

Copyright © Beyondrelational.com