Outputting sql data horizontally

Question

Nevermind.. I'm on Oracle 10g, which means none of the easy concat functions of newer Oracle versions or other SQL versions will work.

I'm looking through one table(payroll) that has duplicates in a column (process_date). The DB is an Oracle DB.

For example table Payroll:

Yr  |  Week |  Process_date
15     53       12/19/2015
15     52       12/19/2015
15     51       12/7/2015

I would want the output to return the weeks that had the same processing date with the week numbers on the same line like this

Yr  |  Week 1|  Week 2 | Process_date
15     53       52        12/19/2015

I've tried various sub-selects and joins, but can't get it right.

EDIT: Added detail EDIT2: Ended topic

Which database are you using? And it sounds like you want to `pivot` your results. If you know the number of potential columns, there are a couple ways to do this depending on the rdbms. If you don't know the number of columns, then you'll need to use `dynamic sql`. — sgeddes, Feb 16 '16 at 21:56
This is a crosstab or pivot query. How to make this happen will depend strongly on which RDBMS you are using. You should update your question to tag the RDBMS (mysql vs sql-server vs postgresql vs...) — JNevill, Feb 16 '16 at 21:56
@sgeddes, that is what I was trying to avoid. I can just export the results to excel and pivot it there.. much easier for me — Craig B, Feb 17 '16 at 14:14

score 1 · Accepted Answer · edited May 23 '17 at 10:28

SELECT Yr, GROUP_CONCAT(Week SEPARATOR ',') Weeks, Process_date
FROM payroll 
GROUP BY Yr, Process_date;

This will give you the following output:

Yr | Weeks | Process_date
15   53,52   12/19/2015

If you must get them in separate columns, you may need a subquery as follow. This query will not work properly if you only have one week, not two.

SELECT Yr, SUBSTRING_INDEX(SUBSTRING_INDEX(Weeks, ',', 1), ',', -1) Week1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(Weeks, ',', 2), ',', -1) Week2, Process_date
FROM
    (SELECT Yr, GROUP_CONCAT(Week SEPARATOR ',') Weeks, Process_date
    FROM payroll 
    GROUP BY Yr, Process_date) merged_payroll;

Output:

Yr | Weeks | Process_date
15   53,52   12/19/2015
15   51,51   12/7/2015  (DUPLICATED 51)

Reference: (1) How to use GROUP_CONCAT in a CONCAT in MySQL (2) Split value from one field to two

This `SELECT Yr, GROUP_CONCAT(Week SEPARATOR ',') Weeks, Process_date` gives me this error `ORA-00907: missing right parenthesis` — Craig B, Feb 17 '16 at 14:38
Nevermind.. I'm on Oravle 10g, which means non of the easy concat functions of newer Oracle versions or other SQL versions will work. Thanks for the help though. — Craig B, Feb 17 '16 at 15:06

Outputting sql data horizontally

1 Answers1