SQL Server, View using multiple select statements

Question

I've banging my head for hours, it seems simple enough, but here goes:

I'd like to create a view using multiple select statements that outputs a Single record-set Example:

CREATE VIEW dbo.TestDB
AS
SELECT     X AS 'First'
FROM       The_Table
WHERE     The_Value = 'y'

SELECT     X AS 'Second'
FROM       The_Table
WHERE     The_Value = 'z'

i wanted to output the following recordset:

Column_1 | Column_2
'First'    'Second'

any help would be greatly appreciated! -Thanks.

Answer 1

A union might be what you want, but keep in mind you need to have the exact same columns so you would need to add a null column to each select.

SELECT     X AS 'First', Null as 'Second'
FROM       The_Table
WHERE     The_Value = 'y'
Union
SELECT     null as 'First', X AS 'Second'
FROM       The_Table
WHERE     The_Value = 'z'

This will combine the two results and give you only the unique combinations. My guess is that this isn't what you are looking for.

Is there a reason why you can't accomplish this in one query or do some subqueries perhaps? Maybe you can provide a more concrete example.

Answer 2

if you want this:

Column_1 | Column_2
'First'    null
null       'Second'

you can use the UNION like suggested in the other answers, but if you really want then on the same row like in your question:

Column_1 | Column_2
'First'    'Second'

try something like this:

CREATE VIEW dbo.TestDB
AS

SELECT
    dt.First,dt2.Second
    FROM (SELECT
              X AS 'First',ROW_NUMBER() OVER(ORDER BY X) AS RowNumber
              FROM The_Table
              WHERE The_Value = 'y'
         ) dt
        LEFT OUTER JOIN (SELECT
                             X AS 'Second',ROW_NUMBER() OVER(ORDER BY X) AS RowNumber
                             FROM The_Table
                         WHERE The_Value = 'z'
                        ) dt2 ON dt.RowNumber=dt2.RowNumber
go

I'm not sure how to join the tables, no info about PKs or how to join them is given.

Answer 3

Untested

CREATE VIEW dbo.TestDB
AS
SELECT     CASE The_Value  when 'y' then x else NULL END AS 'First',
           CASE The_Value  when 'z' then x else NULL END AS 'Second'
FROM       The_Table
WHERE     The_Value in( 'y','z')