question

donw5288 avatar image
donw5288 asked

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.
rowoutput
1 comment
10 |1200

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

This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
virtualjosh avatar image
virtualjosh answered
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 ;
10 |1200

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

donw5288 avatar image
donw5288 answered
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.
10 |1200

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

Squirrel avatar image
Squirrel answered
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
10 |1200

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

virtualjosh avatar image
virtualjosh answered
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
10 |1200

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 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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