One more SQL puzzle

Question

Post reply

One more SQL puzzle

Sandeep-248595

SSC Enthusiast

Points: 176
More actions
March 7, 2007 at 2:54 am

#116397

Take 2 tables
Table1
id Name
1 AAA
2 AAA
3 AAA
Table2
id Name
null BBB
null BBB
null BBB

The final output should be
id Name
1 AAA
2 AAA
3 AAA
4 BBB
5 BBB
6 BBB

Good luck,
Sandeep

Viewing 11 posts - 1 through 10 (of 10 total)

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

Kumar-386306 Default port Points: 1464 More actions · Answer 1

Sandeep,

check out with this Query.

Insert into Table3 (Name)

Select Name from Table1

Insert into Table3 (Name)

Select Name from Table2

Best Regards

Senthil Kumar

AO U SSC Veteran Points: 271 More actions · Answer 2

set id column's identity property "yes" in table1

insert into table1 (Name)

select name from table2

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

CREATE TABLE #Output (ID INT NOT NULL IDENTITY(1,1), NAME VARCHAR(50))

INSERT INTO #Output

SELECT Name FROM Table1

UNION ALL

SELECT Name FROM Table2

SELECT * FROM #Output

DROP TABLE #Output

Prasad Bhogadi
www.inforaise.com

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

This is more of an ordering issue on a union than a puzzle. There doesn't appear to be a relationship to the tables. Is there some reason you chose this as a puzzle?

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 5

Hi ,

Try this script . it will solve ur problem..

select * from tab_a

union

select distinct j.* from (

select distinct z.b,k.name from(

select * from (

select a.i_d,count(*) b from (

select * from tab_a a

union all

select * from tab_b b)a

cross join

(select * from (

select * from tab_a a

union all

select * from tab_b b)a)b

where isnull(a.i_d,'') >= isnull(b.i_d,'')

group by a.i_d)x

where x.i_d is not null)z

cross join

(select * from (

select * from tab_a a

union all

select * from tab_b b)k)k)j

join

tab_b w

on j.name=w.name

Regards ,

Amit Gupta

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Looks more like homework to me...

--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)

Pawan Kumar Khowal Old Hand Points: 371 More actions · Answer 7

SELECT ROW_NUMBER() OVER (ORDER BY NAME) Id , Name FROM

(

SELECT Id,Name FROM [dbo].[Table1]

UNION ALL

SELECT Id,Name FROM [dbo].[Table2]

)r

Pawan Khowal

_________________________________________________________________________________________________________

https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

Regards,
Pawan Kumar Khowal
MSBISkills.com

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Pawan Kumar Khowal (6/13/2016)
SELECT ROW_NUMBER() OVER (ORDER BY NAME) Id , Name FROM
(
SELECT Id,Name FROM [dbo].[Table1]
UNION ALL
SELECT Id,Name FROM [dbo].[Table2]
)r
Pawan Khowal
_________________________________________________________________________________________________________
https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

1. SQL 2000

2. Topic from 2007.

_____________
Code for TallyGenerator

ammit.it2006 Ten Centuries Points: 1280 More actions · Answer 9

I also tried something similar..............:-P

select ROW_NUMBER() over(order by id) , name From Table1

union

select ROW_NUMBER() over(order by id) +3, name From Table2

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 10

ammit.it2006 (7/2/2016)
I also tried something similar..............:-P
select ROW_NUMBER() over(order by id) , name From Table1
union
select ROW_NUMBER() over(order by id) +3, name From Table2

Unfortunately, that presupposes the esoteric knowledge that you have 3 items in Table1. Not exactly flexible code.

Also, since there is the possibility of duplicates between the two tables, the use of UNION could return incorrect results.

--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)

Table1
id	Name
1	AAA
2	AAA
3	AAA

Table2
id	Name
null	BBB
null	BBB
null	BBB

id	Name
1	AAA
2	AAA
3	AAA
4	BBB
5	BBB
6	BBB