Oracle SQL - Convert given list (CSV) into Rows
- Joel Lipman
- Oracle PL/SQL
- Hits: 7868
Situation
I have one column of values that I've suffixed a comma to but I want to display this as a rows in a table. This is for Oracle PL/SQL.
InstanceName | nvarchar(38) | NOT NULL | Name of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER |
ReportID | uniqueidentifier | NULL | The ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times). |
UserName | nvarchar(260) | NULL | Windows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername) |
RequestType | bit | NOT NULL | User or System. Can be 1 or 0. This was zero "0" when I would run a report as a user. |
Format | nvarchar(26) | NULL | This is the rendering format. Mostly RPL if viewed in MS Internet Explorer. |
Parameters | ntext | NULL | Parameters and the values they were submitted with. |
TimeStart | datetime | NOT NULL | Time report started to run. |
TimeEnd | datetime | NOT NULL | Time report finished running? Need to check what finished? |
TimeDataRetrieval | int | NOT NULL | Milliseconds spent retrieving the data. |
TimeProcessing | int | NOT NULL | Milliseconds spent processing the report. |
TimeRendering | int | NOT NULL | Milliseconds spent rendering the report. |
Source | int | NOT NULL | Source of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History) |
Status | nvarchar(32) | NOT NULL | either rsSuccess or an error code; if multiple errors occur, only the first error is recorded |
ByteCount | bigint | NOT NULL | Size of rendered reports in bytes. |
RowCount | bigint | NOT NULL | Number of rows returned from queries. |
Situation:
I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are academic week numbers. So I can't use the built-in functions.
The current structure looks similar to this:
ID SetID WeekNumber StartDate --------- ---------------- ---------------------- ---------------------------- 1 2011/2012 1 2011-07-18 00:00:00 ... 52 2011/2012 52 2012-07-09 00:00:00
The Why
So I find myself writing increasingly complex SQL scripts and it's at the stage where we need to optimize the queries because some scripts are noticeably slow (as observed by the customer...) and then others not.
The What
I'm going to run these benchmark tests against a system that is both up and running via the front-end and back-end. It's MediaWiki CMS used by Wikipedia.org and the like. I like queries against this database because it involves linking a lot of tables and outputting... just articles and their titles.
I have another table holding the audit trail of content approvers on the system. Approvers can e-sign an article (approving it) by clicking on a button. We want to bring back the articles that aren't listed in the audit table (articles yet to be approved).
=FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate)
=FormatDateTime(Fields!BirthDate.Value, DateFormat.GeneralDate) =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongDate) =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate) =FormatDateTime(Fields!BirthDate.Value, DateFormat.LongTime) =FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortTime)
An error occurred during local report processing. An error has occurred during report processing. The Group expression used in grouping 'table1_month' returned a data type that is not valid.
Basically, I've started using so much more SQL in our new Business Intelligence projects that I've been revising all my scripts to see what SQL I can optimize.
One of my systems is a MediaWiki CMS which is used for the official Bournemouth University Knowledge Base. The skin itself is the index page loaded for any page in the Wiki system. It logs the IP address (VisitorIP), the URL (VisitorURL) entered via the browser useragent (VisitorUAgent), the User ID (VisitorID, 0 if not logged in) and of course the Timestamp (DateTimeStamp).
Previously For a basic Oracle function, visit my article Basic Oracle Function Structure. For a more advanced version...
What? This article describes a function that will return random data based on a given value. The function intends to...
What? This is an article to remind me how to modify a column in a database table the old fashioned way (as in stop...
What? So this is a quick article on how to install the mySQL add-on for Oracle SQL Developer v3.0.04. Why? I've used...
Applies to Transact-SQL (T-SQL) What? This is a quick article on how to split a single row into multipe rows based on...
What? This is an article to document how to output the most frequently used words in a MySQL database column. How? Note this...
What? This is a quick article on how to use a counter added to the end of a field column of data. Eh. What? Assume the following...
uploaded display file used license first code time script value order files form date case create system data using name where find user zoho list database server joomla page error mysql creator following parameter deluge google windows added client need report would version website function work table note source field JoelLipman.Com