How merge the result set of two select statement into two columns in single result set?
-
How can I merge the result of two select statement into a single result set.
my query is as
Select (SELECT c.name AS column_nameFROM AADHAR_KYR.sys.tables AS t
INNER JOIN AADHAR_KYR.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where t.name='EID_UID_MAPPING' ) as [Col1],
(SELECT c1.name AS column_name1 FROM AADHAR_KYR.sys.tables AS t1
INNER JOIN AADHAR_KYR.sys.columns c1 ON t1.OBJECT_ID = c1.OBJECT_ID
where t1.name='EID_UID_MAPPING' ) as [Col2]
this showing me error message.
Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while I want the result is as
Col1 Col2
1 1
2 2
3 3
4 4
5 5
6 6
-
Like this:
Select col1.column_name,col2.column_nameFROM
(SELECT row_number() over (order by c.name) rnum,
c.name AS column_name
FROM AADHAR_KYR.sys.tables AS t
INNER JOIN AADHAR_KYR.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
where t.name='EID_UID_MAPPING' ) as [Col1],
(SELECT row_number() over (order by c.name) rnum,
c1.name AS column_name1 FROM AADHAR_KYR.sys.tables AS t1
INNER JOIN AADHAR_KYR.sys.columns c1 ON t1.OBJECT_ID = c1.OBJECT_ID
where t1.name='EID_UID_MAPPING' ) as [Col2]
WHERE col1.rnum = col2.rnum
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
hello
This query is work's for me but only the case where number of result set in both select statement are same but in my case issue is that number of rows are fixed for Column [Col1] but in column [col2] number of row may very. and I need all Rows for col1 and all rows for col2 ether it is equal less or greater.
-
Kindly post some sample data and expected result. I will get back to you
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
amitsingh308 (7/26/2012)
helloThis query is work's for me but only the case where number of result set in both select statement are same but in my case issue is that number of rows are fixed for Column [Col1] but in column [col2] number of row may very. and I need all Rows for col1 and all rows for col2 ether it is equal less or greater.
Here's an old trick from the toolbox of every report developer: set up a 'master' table source which contains all of the values from both result sets.
;WITHQuery1 AS (
SELECT c.name AS column_name
FROM AADHAR_KYR.sys.tables AS t
INNER JOIN AADHAR_KYR.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name='EID_UID_MAPPING'
),
Query2 AS (
SELECT c.name AS column_name
FROM AADHAR_KYR.sys.tables AS t
INNER JOIN AADHAR_KYR.sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name='EID_UID_MAPPING'
)
SELECT names.[name], q1.*, q2.*
FROM (
SELECT DISTINCT [name]
FROM Query1
UNION
SELECT [name]
FROM Query2
) Names
LEFT JOIN Query1 q1 ON q1.[name] = Names.[name]
LEFT JOIN Query1 q2 ON q2.[name] = Names.[name]
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM, you pulled that trick out of the bag just in time. I needed this for something I worked on today. Thank you taking the time to put it up here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply