Sql Query Suggestions

[MegaPowerRockstar]

Hi All,

I am looking for a query for a certain scenario mentioned below.

I have a table person and the query below shows the Original Result.

I am looking for a query that would populate result as shown in Expected result.

I have a solution using union all, but looking for a better query so i dont need to run twice query.

My Solution using Union all

Select Id, Name, City from Person

Union all

Select AddressId, Name, City from Person

 

Query:

Select * from Person

Original Result:

Id   Name   AddressId     City 

1   Joseph   10              Erie

2   Tom       20  Pittsburgh

 

Expected Result:

Id    Name       City

1     Joseph    Erie

10   Joseph    Erie

2    Tom         Pittsburgh

20   Tom  Pittsburgh

 

 

Please provide your result or comments.

 

[MegaPowerRockstar]

hmm..all sleepings aa..will lift tomorrow

[mettastar]

Simple ga aithe

Select Id, name, city from persons
Union
Select address_I'd , name , city from persons

[mettastar]

There is another way of doing it.. horizontal pivot cheyali..

[150bryant]

Original Result:

Id   Name   AddressId     City 

1   Joseph   10              Erie

2   Tom       20  Pittsburgh

 

select id, name, city from table 

will give-- 

result 1

1, joseph , erie

 

select adress id, name, city from table

will give--

result 2

10, joseph, erie

 

 

when we union both result sets we get what you are asking..

like mettastar mention...

do the union

[MegaPowerRockstar]

Original Result:
Id Name AddressId City
1 Joseph 10 Erie
2 Tom 20 Pittsburgh

select id, name, city from table
will give--
result 1
1, joseph , erie

select adress id, name, city from table
will give--
result 2
10, joseph, erie


when we union both result sets we get what you are asking..
like mettastar mention...
do the union

As mentioned, I have the solution using union, but in that I need to run twice query....
Looking for better option, running query once...

[MegaPowerRockstar]

There is another way of doing it.. horizontal pivot cheyali..

Can you show the query? I googled but proper scenario doraka ledhu..will try

[loveindia]

UNION ALL is one way...

you can use APPLY (keyword) in SQL SERVER and do the same....

 

May I know what is the the scenario made you do this...???

 

Using APPLY you can do this way..

 

DECLARE @Person TABLE
(
ID INT, Name VARCHAR(20), AddressID INT, City VARCHAR(10)
)

INSERT INTO @Person VALUES (1, 'Joseph', 10, 'Erie'), (2, 'Tom', 20, 'Pittsburgh')


SELECT CASE p1 WHEN 0 THEN p.ID
               WHEN 1 THEN p.AddressID END AS Id, p.Name, p.City
FROM @Person p
CROSS APPLY (VALUES(0), (1)) AS p1(p1)

 

There is no way you can do this using PIVOT....

[mettastar]

UNION ALL is one way...
you can use APPLY (keyword) in SQL SERVER and do the same....
 
May I know what is the the scenario made you do this...???
 
Using APPLY you can do this way..
 
 

DECLARE @Person TABLE
(
ID INT, Name VARCHAR(20), AddressID INT, City VARCHAR(10)
)

INSERT INTO @Person VALUES (1, 'Joseph', 10, 'Erie'), (2, 'Tom', 20, 'Pittsburgh')


SELECT CASE p1 WHEN 0 THEN p.ID
               WHEN 1 THEN p.AddressID END AS Id, p.Name, p.City
FROM @Person p
CROSS APPLY (VALUES(0), (1)) AS p1(p1)

 
There is no way you can do this using PIVOT....

Horizontal pivot ante.. Columns in Rows cheyadam bhayya.. nenu PIVOT function vadali ani cheppaledu..

@TS... Oracle lo solution kindha SQL..

select case when ROW_NUM=1 Then x.ID ELSE X.AddressID End as ID
, X.NAME
,X.City
from person x
cross join
(select rownum as ROW_NUM from (select 1 from dual group by cube(2,4,8,16)) where rownum<=2)CJT

[loveindia]

Horizontal pivot ante.. Columns in Rows cheyadam bhayya.. nenu PIVOT function vadali ani cheppaledu..

@TS... Oracle lo solution kindha SQL..

select case when ROW_NUM=1 Then x.ID ELSE X.AddressID End as ID
, X.NAME
,X.City
from person x
cross join
(select rownum as ROW_NUM from (select 1 from dual group by cube(2,4,8,16)) where rownum<=2)CJT

 

ok naaku oracle no idea... but Microsoft sql server lo aitey PIVOT use cheylemu annau...
 

[mettastar]

ok naaku oracle no idea... but Microsoft sql server lo aitey PIVOT use cheylemu annau...

Ohk.. PIVOT function undhani teledu baa naku..

[MegaPowerRockstar]

Thanks for all the feedback....

[Shaaru_Bharu]

nice thread