CODEWITHSUNDEEP
sqlsql-servert-sqlview
phil
phil

Reputation: 11

SQL Server, View using multiple select statements

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.

Upvotes: 1

Views: 48692

Answers (3)

SQLMenace
SQLMenace

Reputation: 134961

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

Upvotes: 0

KM.
KM.

Reputation: 103589

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.

Upvotes: 2

Greg Roberts
Greg Roberts

Reputation: 2562

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.

Upvotes: 2

Related Questions