Trying to Transpose Columns to Rows w/ Field Names 1

[LarrySteele]

I'm trying to transpose a query resultset and I'm struggling finding what I'm looking for in Google.

I'm developing an in-house application that inventories applications that our division supports. The inventory will include bringing in information from disparate data sources and pushing everything on a few web pages. Not rocket science...for the most part.

I'm struggling with one of these data sources, which is a spreadsheet that's approaching 100 columns, of which, the business is interested in 30. Easy enough to bring that into Oracle, but displaying that many columns on a webpage is...unwieldy.

Fortunately, this data source will return one and only one row per application, which makes it a candidate for transposing rows (including field names) to columns. The result set would have two columns: field name and field data. Hopefully the following sample will clarify:



Here's what a record might look like in the table:
[pre]ID APP_NAME APP_DESCRIPTION FIELD4 FIELD30
1000 Data Dyamics Lorem ipsum dol... Foo Bar[/pre]



Here's what I'd like to see in the query's result set:
[pre]ID 1000
APP_NAME Data Dyamics
APP_DESCRIPTION Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris blandit.
FIELD4 Foo
... ...
FIELD30 Bar[/pre]


I don't know if that's doable in an Oracle query and that's okay. There are plenty of other ways of getting the same result but I figured I'd start with the query.

Thanks in advance for any suggestions.

 

[SkipVought]

Hi,

The problem doing a Transpose in a query is mixed data types in a column. I suppose converting number to text digits is possible.

a spreadsheet that's approaching 100 columns

Your spreadsheet app may have a feature to query the requisite 30 columns within a workbook or from one workbook to another.

Your spreadsheet app may also have a COPY n PASTE Transposed.

Excel can certainly perform both. Post your question in forum68.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[carp]

Well, this is ugly and, on the surface, looks like a lot of overhead. But it may be a lot more efficient than trying to interface your database with some other application to get your output.
This also assumes that you are only doing one app per page display.
So - with all of these caveats:

SELECT v.parameter, v.p_value as value FROM (
SELECT 'ID' AS parameter, to_char(id) AS p_value, app_name FROM my_table
UNION ALL 
SELECT 'App Name', app_name, app_name FROM my_table
UNION ALL 
SELECT 'App Description', app_description, app_name FROM my_table
UNION ALL 
SELECT 'Field 4', field_4, app_name FROM my_table
UNION ALL 
...
SELECT 'Field 30', field_30, app_name FROM my_table
) v
INNER JOIN my_table mt
   ON mt.app_name = v.app_name
WHERE mt.app_name = 'Data Dynamics';

The key to making this work is to cast all non-character datatypes to characters.

 

[Andrzejek]

You can get the list of columns in your table by:

[TT]SELECT COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE table_name = [red]'YOUR_TABLE_NAME'[/red]
ORDER BY column_id[/TT]

and then you can loop thru them to build your Select statement on-the-fly (what carp suggested)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[LarrySteele]

Thanks for the responses. I'm glad I checked here because I didn't get my notifications.

Skip - you are correct, I'm essentially trying to mimic Excel's transpose operation. Of course Excel doesn't have to contend with datatype conflicts the way an Oracle query would. Alas, Excel is not an option since we're building a website that will need to this transpose records on the fly.

Carp - that's pretty much what I was thinking and yes, it's a LOT of overhead to write but it would work. For some reason I hadn't thought of using union queries even though I frequently use them. The more I think about it, the more this actually makes sense for this unique need.

Andy - yes, that would be a good way to make this query dynamic. The more I think about it, the more I like this idea - that way any table modifications would not require application changes to "see" the new fields or break because an old field is gone.

 

[LarrySteele]

I ended up going with Carp's suggestion. While I like Andy's idea of pulling the column names dynamically, SQL alone does not provision ability to use variables as for fieldnames. I could do this in a proc but just sticking to vanilla SQL for this.

Thanks again for pointing me in the right direction for the solution.

 

[LarrySteele]

Here's the query I'm building, which gives me exactly what I need:

[tt]select s01.seq,
s01.cn,
s01.cv
from (select 1 as seq, 'ID' as cn, to_char(id) as cv, id from schema.apps
union
select 2 as seq, 'APP_NAME' as cn, to_char(app_name) as cv, id from schema.apps
union
select 3 as seq, 'APP_DESCRIPTION' as cn, to_char(app_description) as cv, id from schema.apps) s01
where exists (select 1 from schema.apps h where s01.id = h.id and h.id = '12345');[/tt]

I probably won't keep SEQ in the final output but it will be used by the parent query to order the columns. You my already guess, CN = column name and CV = column value.

Time to switch to Excel to build the remaining select statements in s01.

 

[Andrzejek]

You do know that you may use aliases for your columns in first Select, and you don't have to have them in subsequent Select(s) in the Union, right?

select s01.seq, s01.cn, s01.cv from (
select 1 as seq, 'ID' as cn, to_char(id) as cv, id from schema.apps
union
select 2 [highlight #FCE94F]as seq[/highlight], 'APP_NAME' [highlight #FCE94F]as cn[/highlight], to_char(app_name) [highlight #FCE94F]as cv[/highlight], id from schema.apps
union
select 3 [highlight #FCE94F]as seq[/highlight], 'APP_DESCRIPTION' [highlight #FCE94F]as cn[/highlight], to_char(app_description)[highlight #FCE94F] as cv[/highlight], id from schema.apps) s01
where exists (select 1 from schema.apps h where s01.id = h.id and h.id = '12345');

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[LarrySteele]

Andy,

As a matter of fact, I do know that. I'll blame it on OCD.

 

[carp]

You will eliminate a fair amount of overhead if you use UNION ALL instead of UNION.
Since each query will return distinct values, there is no need to incur the sorting/duplicate removal incurred by UNION.