# question

## How can I output multiple select fields to single row per field.

I have a table tablex that contains rows of data like field1, field2, field3. If I use this: select field1, field2, field3 from tablex I get: field1 field2 field3 as a row of data How can I get: field1 in row 1 field2 in row 2 field3 in row 3 Probably simple, but can't find answer that works.
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
0 Likes 0 ·

·
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 ;

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

untitled.jpg (91.2 KiB)
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
I will point out, be aware you will run into casting problems if not all your fields are of the same data type. You can not SELECT int UNION ALL SELECT float UNION ALL SELECT varchar
0 Likes 0 ·