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.
| Author |
Topic |
|
Sep410
Posting Yak Master
117 Posts |
 Posted - 2008-05-16 : 16:09:38
|
| Hi,I have a view in my database which returns:ID Date Area101 0712008 F102 0712008 F103 0712008 FI should change this view’s output in such a way that for each Id I should have 3 record:ID Date Area Type101 0712008 F LO101 0712008 F PO101 0712008 F SO102 0712008 F LO102 0712008 F PO102 0712008 F SO103 0712008 F LO103 0712008 F PO103 0712008 F SOLO ,PO and SO are constant.How should I do that?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-16 : 16:12:16
|
| CROSS JOIN to a derived table that contains those 3 values.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
 |
|
|
Sep410
Posting Yak Master
117 Posts |
Posted - 2008-05-16 : 16:14:26
|
| There is no table for these 3 items. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-16 : 16:19:49
|
| Which is why it would be a derived table. SELECT ... FROM ... CROSS JOIN (SELECT 'LO' UNION SELECT 'PO' UNION SELECT 'SO') dt...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
Sep410
Posting Yak Master
117 Posts |
Posted - 2008-05-16 : 16:32:01
|
| Dear Tara,I tried this:Select * From View1cross join (SELECT 'LO' UNION SELECT 'PO' UNION SELECT 'SO') dt But I get an error which says: No column was specified for column 1 of dtWhat should I do? |
|
|
|
Sep410
Posting Yak Master
117 Posts |
Posted - 2008-05-16 : 16:45:34
|
| I found it.Select * From View1cross join (SELECT 'LO' as lo UNION SELECT 'PO' as po UNION SELECT 'SO' as so) dtThank you Tara |
|
|
|
|
|
|
View