Complex Union View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complex Union View

Author

Topic

MWilliams1188
Starting Member

1 Post

Posted - 2011-10-31 : 16:09:30

I've got 16 tables: Table1, Table2 ... Table16

Each has 3 fields: City, State, and source (name of Table)

I need to create a master view that lists all records and places an 'X' to denote which table it came from.

The output should have the records as the rows, and the columns as follows:

City, State, Table1, Table2.... Table16, with the 'X' for each record under the column of the table it came from.

EXAMPLE:
City State Table1 Table2
City1 State1 X null
City2 State2 null X
...

I tried using 16 UNION queries, but the number of columns does not match (3 for each table vs. 18 for the master).

I know of several long ways I could get this done, but was wondering if there was something I was overlooking.

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-31 : 17:28:57

[code]CREATE VIEW CityStatePivot AS
SELECT City, State, Table1, Table2, Table3, Table4, Table5, Table6, Table7, Table8, Table9,
Table10, Table11, Table12, Table13, Table14, Table15, Table16
FROM (SELECT City, State, 'Table1' Source, 'X' Val FROM Table1 UNION ALL
SELECT City, State, 'Table2' Source, 'X' Val FROM Table2 UNION ALL
SELECT City, State, 'Table3' Source, 'X' Val FROM Table3 UNION ALL
SELECT City, State, 'Table4' Source, 'X' Val FROM Table4 UNION ALL
SELECT City, State, 'Table5' Source, 'X' Val FROM Table5 UNION ALL
SELECT City, State, 'Table6' Source, 'X' Val FROM Table6 UNION ALL
SELECT City, State, 'Table7' Source, 'X' Val FROM Table7 UNION ALL
SELECT City, State, 'Table8' Source, 'X' Val FROM Table8 UNION ALL
SELECT City, State, 'Table9' Source, 'X' Val FROM Table9 UNION ALL
SELECT City, State, 'Table10' Source, 'X' Val FROM Table10 UNION ALL
SELECT City, State, 'Table11' Source, 'X' Val FROM Table11 UNION ALL
SELECT City, State, 'Table12' Source, 'X' Val FROM Table12 UNION ALL
SELECT City, State, 'Table13' Source, 'X' Val FROM Table13 UNION ALL
SELECT City, State, 'Table14' Source, 'X' Val FROM Table14 UNION ALL
SELECT City, State, 'Table15' Source, 'X' Val FROM Table15 UNION ALL
SELECT City, State, 'Table16' Source, 'X' Val FROM Table16) a
PIVOT(Max(Val) FOR Source IN(Table1, Table2, Table3, Table4, Table5, Table6, Table7, Table8, Table9,
Table10, Table11, Table12, Table13, Table14, Table15, Table16)) b[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources