Excel VBA - How to pivot sql result

Question

I'm doing SQL query with Excel VBA. Below is the SQL string that I'm using:

SELECT DESC, SEQNUM, INSTRUCTION FROM TABLE
WHERE DESC = 'ITEM1' AND SEQNUM <=3

Here is the query result:

DESC    SEQNUM   INSTRUCTION
ITEM1   1        002.000
ITEM1   2        137.000
ITEM1   3        005.000

How can I make the query result looks like this?

DESC   SEQNUM1    SEQNUM2    SEQNUM3
ITEM1  002.000    137.000    005.000

Are you using Access or SQL? Does this [MS Article](https://support.office.com/en-za/article/Create-a-PivotTable-to-analyze-external-data-db50d01d-2e1c-43bd-bfb5-b76a818a927b) work? — PaulFrancis, May 19 '15 at 09:37
I'm writing VBA code in Excel, which will query data from an oracle DB and return the result to active worksheet for analysis. As data analysis will be done by VBA, creating a pivot table in excel doesn't help... — cml, May 20 '15 at 06:20

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

Like this, provided there is one DESC-SEQNUM each:

SELECT DESC, S1.INSTRUCTION, S2.INSTRUCTION, S3.INSTRUCTION
FROM TABLE AS S1 INNER JOIN 
TABLE AS S2 ON S1.DESC = S2.DESC 
INNER JOIN TABLE AS S3 ON S3.DESC=S1.DESC

For Access SQL you may need to enclose the INNER JOINs like mentioned here: SQL multiple join statement The result for Access SQL would be:

SELECT DESC, S1.INSTRUCTION, S2.INSTRUCTION, S3.INSTRUCTION
FROM (TABLE AS S1 INNER JOIN 
TABLE AS S2 ON S1.DESC = S2.DESC) 
INNER JOIN TABLE AS S3 ON S3.DESC=S1.DESC

As mentioned in the comments this assumes that there are not missing allocations to DESC. If there would be you would need to change the INNER JOINs to LEFT JOINs like this:

SELECT DESC, S1.INSTRUCTION, S2.INSTRUCTION, S3.INSTRUCTION
FROM (TABLE AS S1 LEFT JOIN 
TABLE AS S2 ON S1.DESC = S2.DESC) 
LEFT JOIN TABLE AS S3 ON S3.DESC=S1.DESC

edited May 23 '17 at 11:58

Community

1
1

answered May 19 '15 at 09:47

AnalystCave.com

4,884
2
22
30

Will work with the provided sample data, but will not return anything when one sequence (or more) is missing. – jarlh May 19 '15 at 09:49
Correct. But it is just a matter of changing the relevant INNER JOINs to LEFT JOINs when needed. @cml did not provide any additional input as to assume there might be a need for this – AnalystCave.com May 19 '15 at 09:51
Have try all 3 methods but none of them works. Thanks anyway :) – cml May 20 '15 at 01:26

jarlh · Accepted Answer · 2015-05-19T09:58:40.070

If supported - do a self full outer join, ANSI SQL syntax here:

select coalesce(t1.desc, t2.desc, t3.desc),
       t1.INSTRUCTION SEQNUM1,
       t2.INSTRUCTION SEQNUM2,
       t3.INSTRUCTION SEQNUM3
from (select * from TABLE where SEQNUM = 1) t1
  full outer join (select * from TABLE where SEQNUM = 2) t2 on t1.desc = t2.desc
  full outer join (select * from TABLE where SEQNUM = 3) t3 on t2.desc = t3.desc

score 0 · Answer 3 · edited Jun 27 '19 at 07:47

0

Sql = " TRANSFORM  INSTRUCTION " & vbCr & _
      " SELECT     DISTINCT DESC  " & vbCr & _
      " FROM       [Sheet$] " & vbCr & _  
      " GROUP BY   DESC" & vbCr & _
      " order by   DESC asc " & vbCr & _
      " PIVOT      SEQNUM "

// [Sheet$] = your TABLE

edited Jun 27 '19 at 07:47

Himanshu

31,810
31
111
133

answered Jun 27 '19 at 07:27

weeraphap

1

Excel VBA - How to pivot sql result

3 Answers3