Solved

SQL Sybase - Convert Rows to Columns

Posted on 2013-12-02
10
381 Views
Last Modified: 2016-05-27
I have a numerical result for each month in the Current table. Each result is a row. I
I would like to be able to create a result set, so that the months are columns and all the relevant monthly values are in the relevant month column in one row  see Target table structure (below)

Current Table Structure:

ID  Month Result
1    02          9.7
2    03         6.5
3    01           3.2

Target Table Structure:

ID   Jan      Feb     Mar
1      3.2     9.7      6.5

Can anyone give me complete answer that I can simply paste in and adapt? BTW, I am a novice at SQL.
Thanks
PS Not all the MS SQL commands like PIVOT are available in Sybase.
0
Comment
Question by:jbound
  • 4
  • 4
10 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39691061
This will work if you always have values for January.  If not, you'll need to do something a little more complex.  You also may need quotes around the Month values if they are strings.
select jan.ID, jan.Result, feb.Result, mar.Result, apr.Result, may.Result, jun.Result,
    jul.Result, aug.Result, sep.Result, oct.Result, nov.Result, dec.Result
from Current jan, Current feb, Current mar, Current apr, Current may, Current jun,
    Current jul, Current aug, Current sep, Current oct, Current nov, Current dec
where jan.ID *= feb.ID
and jan.ID *= mar.ID
and jan.ID *= apr.ID
and jan.ID *= may.ID
and jan.ID *= jun.ID
and jan.ID *= jul.ID
and jan.ID *= aug.ID
and jan.ID *= sep.ID
and jan.ID *= oct.ID
and jan.ID *= nov.ID
and jan.ID *= dec.ID
and jan.Month = 01
and feb.Month = 02
and mar.Month = 03
and apr.Month = 04
and may.Month = 05
and jun.Month = 06
and jul.Month = 07
and aug.Month = 08
and sep.Month = 09
and oct.Month = 10
and nov.Month = 11
and dec.Month = 12

Open in new window

0
 

Author Comment

by:jbound
ID: 39691164
Sorry, but with my limited knowledge, I can't adapt this code as I am unsure what to substitute.

The real name of the current table is "dba"."vwResultInspTask_iDashboards"

and the actual names of the current columns that are to be transposed are:
ID   nMonth    AnswerDesc
1         02          9.7
2         03         6.5
3         01           3.2

The results are not going to a table, but are simply the query result set. I hope this is clearer.
Rgds
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 350 total points
ID: 39691292
Simple name substitution is all that's needed...
select jan.ID, jan.AnswerDesc Jan, feb.AnswerDesc Feb, mar.AnswerDesc Mar, apr.AnswerDesc Apr, may.AnswerDesc May, jun.AnswerDesc Jun, jul.AnswerDesc Jul, aug.AnswerDesc Aug, sep.AnswerDesc Sep, oct.AnswerDesc Oct, nov.AnswerDesc Nov, dec.AnswerDesc Dec
from "dba"."vwAnswerDescInspTask_iDashboards" jan, "dba"."vwAnswerDescInspTask_iDashboards" feb, "dba"."vwAnswerDescInspTask_iDashboards" mar, "dba"."vwAnswerDescInspTask_iDashboards" apr, "dba"."vwAnswerDescInspTask_iDashboards" may, "dba"."vwAnswerDescInspTask_iDashboards" jun, "dba"."vwAnswerDescInspTask_iDashboards" jul, "dba"."vwAnswerDescInspTask_iDashboards" aug, "dba"."vwAnswerDescInspTask_iDashboards" sep, "dba"."vwAnswerDescInspTask_iDashboards" oct, "dba"."vwAnswerDescInspTask_iDashboards" nov, "dba"."vwAnswerDescInspTask_iDashboards" dec
where jan.ID *= feb.ID
and jan.ID *= mar.ID
and jan.ID *= apr.ID
and jan.ID *= may.ID
and jan.ID *= jun.ID
and jan.ID *= jul.ID
and jan.ID *= aug.ID
and jan.ID *= sep.ID
and jan.ID *= oct.ID
and jan.ID *= nov.ID
and jan.ID *= dec.ID
and jan.nMonth = 01
and feb.nMonth = 02
and mar.nMonth = 03
and apr.nMonth = 04
and may.nMonth = 05
and jun.nMonth = 06
and jul.nMonth = 07
and aug.nMonth = 08
and sep.nMonth = 09
and oct.nMonth = 10
and nov.nMonth = 11
and dec.nMonth = 12

Open in new window

0
 

Author Comment

by:jbound
ID: 39691394
Thanks for new code - but a few glitches :
1. Kept stopping a 'dec', which I think is a reserved word ('decreasing'?), so changed all of the dec to 'december'.
Ran the query:

2. Next error is 'no such table as '"dba"."vwAnswerDescInspTask_iDashboards" - which is correct as the table name is "dba"."vwResultInspTask_iDashboards". So, changed that.
Ran it again:

3. Could not execute statement. Column 'ID' not found. Line 1, Column 1. This table is a view, so there is no ID column- my error, sorry. I have substituted this for the first column of the table/view, which is 'ClientID'.

So the modified query now looks like this:

select jan.ClientID, jan.AnswerDesc Jan, feb.AnswerDesc Feb, mar.AnswerDesc Mar, apr.AnswerDesc Apr, may.AnswerDesc May, jun.AnswerDesc Jun, jul.AnswerDesc Jul, aug.AnswerDesc Aug, sep.AnswerDesc Sep, oct.AnswerDesc Oct, nov.AnswerDesc Nov, december.AnswerDesc December
from "dba"."vwResultInspTask_iDashboards" jan, "dba"."vwResultInspTask_iDashboards" feb, "dba"."vwResultInspTask_iDashboards" mar, "dba"."vwResultInspTask_iDashboards" apr, "dba"."vwResultInspTask_iDashboards" may, "dba"."vwResultInspTask_iDashboards" jun, "dba"."vwResultInspTask_iDashboards" jul, "dba"."vwResultInspTask_iDashboards" aug, "dba"."vwResultInspTask_iDashboards" sep, "dba"."vwResultInspTask_iDashboards" oct, "dba"."vwResultInspTask_iDashboards" nov, "dba"."vwResultInspTask_iDashboards" december
where jan.ClientID *= feb.ClientID
and jan.ClientID *= mar.ClientID
and jan.ClientID *= apr.ClientID
and jan.ClientID *= may.ClientID
and jan.ClientID *= jun.ClientID
and jan.ClientID *= jul.ClientID
and jan.ClientID *= aug.ClientID
and jan.ClientID *= sep.ClientID
and jan.ClientID *= oct.ClientID
and jan.ClientID *= nov.ClientID
and jan.ClientID *= december.ClientID
and jan.nMonth = 01
and feb.nMonth = 02
and mar.nMonth = 03
and apr.nMonth = 04
and may.nMonth = 05
and jun.nMonth = 06
and jul.nMonth = 07
and aug.nMonth = 08
and sep.nMonth = 09
and oct.nMonth = 10
and nov.nMonth = 11
and december.nMonth = 12

and "dba"."vwResultInspTask_iDashboards"."SiteID" = 3771
(to limit the result set).

The error returned is: Correlation name "dba"."vwResultInspTask_iDashboards" not found

So what I have I done wrong?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 26

Expert Comment

by:wilcoxon
ID: 39691413
Sorry about the incorrect view name - global search and replace of strings in bad order (changed Result to AnswerDesc after changing table names).

The problem is your final statement.  You should change it to:
and jan.SiteID = 3771

Open in new window


The alias assigned to a table/view name is the correlation name.  Since all of the tables have been aliased then it is expecting the correlation name (and not the actual table/view name).
0
 

Author Comment

by:jbound
ID: 39691847
Good Morning, seems like we're on the final lap...

I put in all of the code in my previous reply to you and then added : and jan.SiteID = 3771, to limit the result set to one site, ran the query and got this msg:

Transact: SQL Outer joins are currently disabled. SQL Code = 1099, ODBC 3 State = 'HY000'
Line 1, column 1


Is this a general setting in my database or related to this query?
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 39694230
I've never seen that before.  Are you using ASE, SQL Anywhere, or another Sybase product/version?

I found this for SQL Anywhere (if they are deprecated, I have no idea what you'd use instead):

The ability to specify a Transact-SQL outer join in a query is currently disabled by the setting of the tsql_outer_joins connection option. Transact-SQL outer joins are deprecated in this release of SQL Anywhere.
If it is a connection-specific setting, you may be able to change it (how would depend on how you connect to Sybase).  It may also be a server setting in which case sa would need to change it.
0
 

Author Comment

by:jbound
ID: 39694258
Hi, yes you are correct, it is SQL Anywhere.

I also checked the error msg in Sybase's manual, got the same definition, but I don't fully understand it.
I think this problem is now moving some way beyond my skill level so I'll check with my SA to see if outer joins can be done on our server. Either way, I'll get back to you to let you know the result - really appreciate your help so far, thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now