question

SELECT 'field1', field1 FROM tablex UNION ALL SELECT 'field2', field2 FROM tablex UNION ALL SELECT 'field3', field3 FROM tablex ; Not as complex as a PIVOT clause, where you transpose the data based on the actual data within each field. What you want is easy to do with a UNION. However, this may only makes sense if you have a WHERE clause going against a PK. For instance: SELECT 'Product Name' AS [field] , product_name AS [value] FROM Product WHERE product_id = @product_id UNION ALL SELECT 'Product Category' , category_name FROM Product WHERE product_id = @product_id UNION ALL SELECT 'Color' , color FROM Product WHERE product_id = @product_id ;

I had put the union all in my statement, but I have say 10 rows of data in tablex and the three fields field1, field2, field3. Example: a1 a2 a3 b1 b2 b3 c1 c2 c3 d1 d2 d3 ......and so forth What I would like to get out is: a1 a2 a3 b1 b2 b3 c1 c2 c3 .... and so forth. When I write: Select field1 from tablex union all select field2 from tablex union all select field3 from tablex This gives me: (~~~~~ indicates the pattern continues on) All the data from col 1, then col 2 and then col 3. a1 b1 c1 d1 e1 ~~~~~ a2 b2 c2 d2 e2 ~~~~~ a3 b3 c3 ~~~~~~~ I would like to have a query that can get data from different tables and output a text file with one field per row getting all the data out that way, then repeat for the second record in the main query. When I try to put the selects in a select statement which I thought logically would give me what I want, I get an error about: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I hope I am making this problem clear, sorry if it is confusing.

select field = case f when 1 then field1 when 2 then field2 when 3 then field3 end from tablex t cross join ( select f = 1 union all select f = 2 union all select f = 3 ) f order by field, f

You simply need to add an ORDER BY to the UNION'ed result set. -- Sample Table and Data ---------------------------------------------------------------------- DECLARE @Sample TABLE ( [field1] varchar(10) null , [field2] varchar(10) null , [field3] varchar(10) null , [field4] varchar(10) null , [field5] varchar(10) null ); INSERT INTO @Sample VALUES ('1', '2', '3', '4', '5') , ('i', 'ii', 'iii', 'iv', 'v') , ('one', 'two', 'three', 'four', 'five') ; -- Review Sample Data SELECT * FROM @Sample; -- Algorithm ---------------------------------------------------------------------- -- I don't know if your table has a primary key -- In case it doesn't already, add an identity column SELECT IDENTITY(int, 1, 1) AS pk_row , * INTO #Target FROM @Sample ; -- Review Target SELECT * FROM #Target; -- Simple UNION with ORDER by Row and Field SELECT pk_row , 'field1' AS [Field] , field1 As [Value] FROM #Target UNION ALL SELECT pk_row, 'field2', field2 FROM #Target UNION ALL SELECT pk_row, 'field3', field3 FROM #Target UNION ALL SELECT pk_row, 'field4', field4 FROM #Target UNION ALL SELECT pk_row, 'field5', field5 FROM #Target ORDER BY pk_row -- All Row 1, All Row 2, ... , Field -- Always show same Field sequence ; -- Maintennance DROP TABLE #Target; ![alt text][1] [1]: /storage/temp/1592-untitled.jpg