Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Generate IDs Puzzle

In the puzzle you have to combine 2 tables together and generate Ids to the rows for the second table

This puzzle was asked @SQL Server Central. Please check out the sample input and the expected output below-

Sample Input

Table 1

Id Name
1 AAA
2 AAA
3 AAA

Table 2

Id Name
NULL BBB
NULL BBB
NULL BBB

Expected Output

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

Solution 1 | Union All & Row Numbers


--


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


--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements