In SQL, how to write a query such that the table is in wide format and not in long format?

Question

I have two sql software that i am using, Visual Foxpro 7 and MS Access 2007. I wanna know if a query can be written in each of these which will give me a table in wide format. I know it involves subqueries which VFP 7 doesnt support, as far as i know. It might only be possible in Access.

I got two exactly similar files but each pertain to different time periods. One is of Dec-2014 and the other is of Dec-2015. All the other things are same, e.g. field names, structure. Normally when i extract data from both of these files i use multiple SELECT commands (one command for each year) and use UNIONS then export to excel and make pivot tables. But i wanna know if it can be done directly in sql. i tried writing this in Access:

SELECT a.bank, (select sum(a.amount) from adv_1412 as a where a.s_no<>9999 
and a.bank in (1114,1123) group by a.bank), (select sum(b.amount) 
from adv_1512 as b where b.s_no<>9999 and b.bank in (1114,1123) group by 
b.bank)
FROM adv_1412 AS a
WHERE a.bank In (1114,1123)
GROUP BY a.bank;

This returns the error "At most one record can be returned by this subquery". The query works fine when used with only one bank code (in the above code i am taking two bank codes i.e. 1114 and 1123).

I had tried searching for the solution on internet and got some useful stackoverflow links but I couldnt comprehend those answers properly and implement the thing in my own situation.

I am mostly a noob with SQL. Just started working on it. Whatever I know about it, it is all self-taught. So, please bear with me. :D

Answer 1

I think you should be able to do this with a left join, as long as all of the bank codes you want appear in the first table referenced:

select a.bank,
       a.amt as amount_1412,
       b.amt as amount_1512
from (
       select bank, sum( amount ) as amt
       from adv_1412
       where a.s_no<>9999 
             and a.bank in (1114,1123)
       group by bank
     ) as a
     left join
     (
       select bank, sum( amount ) as amt
       from adv_1512
       where a.s_no<>9999 
             and a.bank in (1114,1123)
       group by bank
     ) as b
     on a.bank=b.bank

Answer 2

Please do not make assumptions on what VFP 7 or any other version cannot do while ms access can. In fact, even after 10+ years since VFP development has been stopped, VFP is still too much ahead of ms access (which I don't even classify as a database system - if you would compare their sql capabilities know that VFP is closer to ANSI SQL while ms access has an sql understanding of its own that makes you crazy).

The SQL you tried wouldn't make sense in any SQL database system, including ms access, ms sql server, postgresql, mysql, oracle ... you name it.

It has been a long time I last used VFP 7, I am not sure if it supported subqueries in that sense if it would work after being corrected. However, if you are doing this from inside a VFP7 application (or VFP 7 IDE), you could simply use multiple SQLs instead of subqueries to get the end result you want. i.e.:

Select bank, Sum(amount) As amount ;
    from adv_1412 ;
    where s_no<>9999 And bank In (1114,1123) ;
    group By a.bank ;
    into cursor crsBankA ;
    nofilter

Select bank, Sum(amount) As amount ;
    from adv_1512 ;
    where s_no<>9999 And bank In (1114,1123) ;
    group By bank ;
    into cursor crsBankB ;
    nofilter

Select Nvl(a.bank, b.bank) As bank, a.amount As amountA, b.amount As AmountB ;
    from crsBankA a ;
    full join crsBankB b on a.bank = b.bank

OTOH, be it a VFP5, 6, 7 application or a non-VFP application, it could use the VFPOLEDB and thus utilise the VFP 9's data engine with subqueries and much more ANSI SQL support. Then say you could do this:

Select Nvl(a.bank, b.bank) As bank, a.amount As amountA, b.amount As AmountB ;
    from ;
    (Select bank, Sum(amount) As amount ;
    from adv_1412 ;
    where s_no<>9999 And bank In (1114,1123) ;
    group By a.bank) a ;
    full Join ;
    (Select bank, Sum(amount) As amount ;
    from adv_1512 ;
    where s_no<>9999 And bank In (1114,1123) ;
    group By bank) b On a.bank = b.bank

Having said these, you are saying time period, but in your code there is nothing that deals with periods (those codes and different table names suggest you have a problem in data design but if that works for you then might be acceptable).

You are also saying

export to excel and make pivot tables

Well, even in VFP6 or 7 ... 9, for creating pivot tables in excel:

• You don't need to sum(marize) data. That is what Excel excels at < g > while creating pivots and summarizing data.
• You would pass the data to excel using VFPOLEDB (and thus using VFP9 data engine, not 7).

Here is a sample pivoting using VFP's sample data (I don't remember if I wrote this during VFP7 or 6 days but I know I have been using this infrastructure much before than VFP9 and 8):

*!* Excel Pivot sample
*!* Author:Cetin Basoz
*!* Thank to Marco Plaza for reminding me about saving Recordset as a workaround for 64 bits
Local loADOStream, lcSQL, lcConnStr

TEXT TO m.lcSQL NOSHOW TEXTMERGE PRETEXT 15
SELECT RTRIM(emp.first_name) + ' ' +RTRIM(emp.last_name) as SalesMan,
    cs.company, pr.prod_name as ProductName, oi.quantity, YEAR(od.order_date) as yearOrdered
  FROM  customer cs
  INNER JOIN orders od on cs.cust_id = od.cust_id
  inner join employee emp on od.emp_id = emp.emp_id
  INNER JOIN orditems oi on od.order_id = oi.order_id
  INNER JOIN products pr on oi.product_id = pr.product_id
ENDTEXT
lcConnStr = 'Provider=VFPOLEDB;Data Source='+_samples+'Data\Testdata.dbc'

loADOStream = GetDataAsAdoStream( m.lcConnStr, m.lcSQL)

* Pivot sample
** Assume 64 bit excel and use ADO stream

*#include 'xlConstants.h'
#Define xlCount -4112
#Define xlSum -4157
#Define xlDataField 4
#Define xlExternal 2

lcPageList = 'Company'
lcRowList = 'ProductName,YearOrdered'
lcColList = 'SalesMan'
lcDataField = 'Quantity'

Alines(laRowFields,m.lcRowList,.T.,",")
Alines(laColFields,m.lcColList,.T.,",")
Alines(laPageFields,m.lcPageList,.T.,",")

lcOption = 'sum'

lcFunction = Iif(Lower(Evl(m.lcOption,'')) == "count","COUNT","SUM")
lcCaption   = Iif(Upper(m.lcFunction)='SUM','Quantity Sold','Count')
lnFunction  = Iif(Upper(m.lcFunction)='SUM',xlSum,xlCount)

Local oExcel As 'Excel.Application'
oExcel = Createobject('Excel.Application')
With oExcel
    .Visible = .T.
    .Workbooks.Add
    *-- Destination of the pivottable inside Excel
    .ActiveWorkbook.ActiveSheet.Name = 'Product Sales'
    With .ActiveWorkbook.ActiveSheet
        oDestination = .Range('A1')
        With oExcel.ActiveWorkbook.PivotCaches.Add(xlExternal)
            loRS = Createobject('AdoDb.Recordset')
            loRS.Open( loADOStream )
            .Recordset = loRS
            .CreatePivotTable(oDestination, 'PivotTable')
        Endwith

        With .PivotTables("PivotTable")
            Do Case
                Case !Empty(m.lcRowList) And !Empty(m.lcColList) And !Empty(m.lcPageList)
                    .AddFields(@laRowFields, @laColFields, @laPageFields)
                Case !Empty(m.lcRowList) And !Empty(m.lcColList)
                    .AddFields(@laRowFields, @laColFields)
                Case !Empty(m.lcRowList) And !Empty(m.lcPageList)
                    .AddFields(@laRowFields, , @laPageFields)
                Case !Empty(m.lcRowList)
                    .AddFields(@laRowFields)
                Case !Empty(m.lcColList) And !Empty(m.lcPageList)
                    .AddFields(, @laColFields, @laPageFields)
                Case !Empty(m.lcColList)
                    .AddFields(, @laColFields)
                Case !Empty(m.lcPageList)
                    .AddFields(, , @laPageFields)
            Endcase
            .PivotFields(m.lcDataField).Orientation = xlDataField
            With .Datafields(1)
                .Caption  = m.lcCaption
                .Function = m.lnFunction
            Endwith
            If !Empty(m.lcRowList)
                For ix = 1 To Alen(laRowFields)
                    With .PivotFields(laRowFields[ix])
                        .Subtotals(1) = .T. && To turn of subtotals
                        .Subtotals(1) = .F.
                    Endwith
                Endfor
                .Mergelabels = .T.
            Endif
        Endwith
        .UsedRange.Columns.AutoFit
    Endwith
    If Type('.ActiveWorkbook.ShowPivotTableFieldList') = 'L'
        .ActiveWorkbook.ShowPivotTableFieldList = .F.
    Endif
Endwith

Procedure GetDataAsAdoStream(tcConnection, tcSQL)
    loStream = Createobject('AdoDb.Stream')
    loConn = Createobject("Adodb.connection")
    loConn.ConnectionString = m.tcConnection
    loConn.Open()
    loRS = loConn.Execute(m.tcSQL)
    loRS.Save( loStream )
    loRS.Close
    loConn.Close
    Return loStream
Endproc

.

Answer 3

SELECT h.bank_name AS ["Bank Name"], 
(select sum(a.amount) from adv_1412 as a inner join Banks as d on a.bank = 
d.code where a.s_no<>9999 and d.bank_name = h.bank_name) AS ["Dec-14"], 
(select sum(b.amount) from adv_1512 as b inner join Banks as e on b.bank = 
e.code where b.s_no<>9999 and e.bank_name = h.bank_name) AS ["Dec-15"], 
(select sum(c.amount) from adv_1612 as c inner join Banks as f on c.bank = 
f.code where c.s_no<>9999 and f.bank_name = h.bank_name) AS ["Dec-16"]
FROM adv_1412 AS g INNER JOIN Banks AS h ON g.bank=h.code
WHERE g.bank In (1114,1117,1123,1153,1154,1164,1170,1182,1138,1186)
GROUP BY h.bank_name;

The above is the solution i got to before reading Cetin and Alex's answers. I added some additional things like a join to a Bank Name table to get bank names instead of codes. Also, i added an extra data file pertaining to Dec-16. But ofcourse their answers work faster. Mine just looks too messy. I am probably doing a lot of things wrong in that. But it somehow works. I tried that. Thanks again for the help guys. This was my first ever question on stackoverflow and I was really encouraged by the answers.

Answer 4

A detailed (and needed) explanation of my problem:

I have three identical tables with exact same structures. The only difference is they each have different value in the field "period" (they pertain to Dec-14, Dec-15 and Dec-16 respectively). A sample table is:

+------+------+---------+------------+---------------------------+--------+
| s_no | bank | br_code | period     | other unrelated fields... | amount |
+------+------+---------+------------+---------------------------+--------+
| 1    |1114  | 234     | 12/31/2014 | ...                       | 321321 |
+------+------+---------+------------+---------------------------+--------+
| 2    |1114  | 234     | 12/31/2014 | ...                       | 121231 |
+------+------+---------+------------+---------------------------+--------+
| 3    |1182  | 231     | 12/31/2014 | ...                       | 345451 |
+------+------+---------+------------+---------------------------+--------+
| 4    |1165  | 231     | 12/31/2014 | ...                       | 3781   |
+------+------+---------+------------+---------------------------+--------+
| 5    |1165  | 231     | 12/31/2014 | ...                       | 4656   |
+------+------+---------+------------+---------------------------+--------+
| 6    |1165  | 335     | 12/31/2014 | ...                       | 6786   |
+------+------+---------+------------+---------------------------+--------+
| 7    |1170  | 112     | 12/31/2014 | ...                       | 121211 |
+------+------+---------+------------+---------------------------+--------+
| 8    |1170  | 231     | 12/31/2014 | ...                       | 796754 |
+------+------+---------+------------+---------------------------+--------+
| 9    |1168  | 231     | 12/31/2014 | ...                       | 122    |
+------+------+---------+------------+---------------------------+--------+

So, there are a lot of banks, and each bank has lots of branches and each branch has lots of records. These are the data tables. Now my original desired output was something like this:

+-------+-----------+-----------+-----------+
| Bank  | Dec-14    | Dec-15    | Dec-16    | 
+-------+-----------+-----------+-----------+
| 1114  |65466565   |321312321  |321321313  |
+-------+-----------+-----------+-----------+
| 1123  |9879879879 |654654654  |51951951   |  
+-------+-----------+-----------+-----------+
| 1128  |1211111    |5757576    |65483218   |  
+-------+-----------+-----------+-----------+
| 1154  |98793213   |2132132132 |212668245  |  
+-------+-----------+-----------+-----------+

i.e. i want to get in a single query total amount of selected banks (or each bank, it doesnt matter) for each of the year-ends. I later added a Join to a Bank Name table which would give me Bank Names instead of their codes in the final output (as is evident if you check the "solution" i provided).

Now, I have solved this using multiple answers provided here and they all work. I tried an additional solution which also worked. Here it is:

SELECT bank,

Sum(IIf(period=#12/31/2014#,amount,0)) AS Dec14, 
Sum(IIf(period=#12/31/2015#,amount,0)) AS Dec15, 
Sum(IIf(period=#12/31/2016#,amount,0)) AS Dec16

FROM

(select * from adv_1412 union all select * from adv_1512 union all select * 
from adv_1612)  AS [%$##@_Alias]

WHERE s_no<>9999
GROUP BY bank;

This was written in Access. Any suggestion/improvements regarding my queries will be highly appreciated.

P.S I am not using excel because the data is too large. Plus I dont wanna convert/export all my dbf files to excel, etc

Answer 5

As I can see you are using a table per month. That is called partitioning which some backends do much easier (i.e.: postgreSQL could treat all the months as a single table while there are separate tables per month).

I don't know if you are again making an assumption saying "too large" or "lots and lots of". Those are relative words. What is large and a lot for you (and access - that would make sense to say large for access), might be small for another backend. For example 10 million rows of customer data would be too large for VFP and access (32 bits) to handle, while it is not large for many backends like postgreSQL, oracle, mysql ...

You also sound to be making assumptions on not doing the pivoting in Excel because the data is large. To my experience, even in old hardware, pivoting 200-300,000 rows of data in Excel were taking a few seconds.

Anyway, I don't know the amount < g > of "a lot" so would simply think it as "a lot" in my own terms too. In VFP jargon, pivot is often referred to as "cross tabbing" or "x tabbing". There is even a cross tabbing prg that ships with VFP (I never use that one, prefer my own).

I wrote a "pivoting" sample based on the info you gave. In the sample:

• Creating sample data under bankTest folder.
• Sample data has 5000 banks and each adv_*12 table gets 0-150 transactions per bank.
• After sample data is created it is pivoted (cross tabbed) locally into a cursor (in sample named "myPivot").
• I also added some timing to process. On my computer (AMD Phenom II X6 1090T 3200Mhz, 16 Gb RAM, 7200 RPM WD 500 Gb HDD), the data creation took something like 9.2 seconds ( ~ 400,000 rows per adv table ). The pivoting took 1.4 seconds.
• I tried to write VFP7 compatible code but my memory might have betrayed me, so if something doesn't work, could rewrite that part to be VFP7 compatible.
• Testing code is easy. In a temp folder save it as a prg and run it.

Here is the code:

Close Databases All
Clear All
Clear
Set Safety Off

Start=Seconds()
CreateSampleData(5000, 150, .T.) && 5000 banks, 0-150 rows per bank in adv_ tables, create afresh
? Seconds()-m.start

Start=Seconds()
CreateLocalPivot('myPivot')
? Seconds()-m.start

Select myPivot
Browse Last


Procedure CreateLocalPivot(tcCursorName)

    Local Array laPivot[1]

    Select bankId, Name, 0, 0, 0 From ('bankTest\Banks') ;
        order By bankId ;
        into Array laPivot

    Local ix, cursorName
    For ix = 14 To 16
        cursorName = 'adv_'+Ltrim(Str(m.ix)) + '12'
        Select bank, Sum(amount) As amount ;
            from ('bankTest\'+m.cursorName) ;
            group By bank ;
            into Cursor ('crs'+m.cursorName) ;
            nofilter
        Scan
            laPivot[ Ascan(laPivot,bank,1,-1,1,8), m.ix - 14 + 3 ] = amount
        Endscan
    Endfor

    Create Cursor (m.tcCursorName) (bankId Int, Name c(20), adv_14 Y, adv_15 Y, adv_16 Y)
    Insert Into (m.tcCursorName) From Array laPivot
Endproc


Procedure CreateSampleData(tnNumberOfBanks, tnNumberOfRows, tlFresh)
    If !Directory('bankTest')
        Md 'bankTest'
    Endif

    If File('bankTest\banks.dbf') And !m.tlFresh
        Return
    Endif
    Close Databases All
    Erase ('bankTest\*.*')

    * Create sample data
    * Create imaginary banks table and insert tnNumberOfBanks imaginary banks
    * create imaginary adv_1412, adv_1512, adv_1612 tables
    * tables hold data for Dec-2014, 2015 and 2016 respectively
    * For each bank, randomly insert 0 to tnNumberOfRows rows into each adv_* table
    * irrelevant fields are dropped

    Create Table ('bankTest\banks') (bankId Int, Name c(20))
    Create Table ('bankTest\adv_1412') (s_no Int, bank Int, amount Y)
    Create Table ('bankTest\adv_1512') (s_no Int, bank Int, amount Y)
    Create Table ('bankTest\adv_1612') (s_no Int, bank Int, amount Y)

    Local ix, jx, kx
    Rand(-1)
    For ix=1 To m.tnNumberOfBanks
        Insert Into Banks (bankId, Name) Values (m.ix, 'Bank #'+Ltrim(Str(m.ix)))
        For jx = 14 To 16
            For kx=1 To Int(Rand() * (m.tnNumberOfRows+1)) && 0 to m.tnNumberOfRows
                Insert Into ('adv_' + Ltrim(Str(m.jx)) +'12') (bank, amount) Values (m.ix, Int(Rand() * 10000))
            Endfor
        Endfor
    Endfor
    Select Banks
    Index On bankId Tag bankId
    For ix = 14 To 16
        Select ('adv_' + Ltrim(Str(m.ix)) + '12')
        Replace All s_no With Recno()
        Index On bank Tag bank
    Endfor

    ? Reccount('banks')
    ? Reccount('adv_1412')
    ? Reccount('adv_1512')
    ? Reccount('adv_1612')

    Close Databases All
Endproc

EDIT: Sample using VFP9 data engine from VFP7:

Local oRs, cSQL

* A complex SQL with subqueries
* just for the purpose of demonstrating
* utilizing VFP9 engine from an older version
* This SQL may not be supported in VFP7
* but VFP7 can get the result via VFPOLEDB
* or do updates in the ways that VFP7 doesn't support
* but VFP9 does.
TEXT to cSQL noshow
Select uc.*, uo.totalAmt
from (select cust_id, company, contact from customer where country="USA") uc
left join (
   Select cust_id, Sum(quantity * unit_price) as totalAmt
   from (  Select cust_id, quantity, unit_price from Orders o
           inner join OrdItems oi on o.Order_Id = oi.Order_Id ) amt
   group by cust_id
) uo on uc.cust_id = uo.cust_id
ENDTEXT


oCon = Createobject("AdoDb.Connection")
oCon.ConnectionString = "Provider=VFPOLEDB;Data Source=" + _samples +"data\testdata.dbc"
oCon.Open
oRs = oCon.Execute(m.cSQL)
xDOM = Newobject('MSXML.DOMDocument')
oRs.Save(xDOM, 1)
oRs.Close
oCon.Close
Xmltocursor(xDOM.XML, "MyCursorName", 1028)
Select MyCursorName
Browse