Rows to Column (Column-delimited)

Database
Enthusiast

Rows to Column (Column-delimited)

Hello, I would like to know how to read in the following table contents and output the data in comma-delimited format:

TableA contents:

Account#        Date            Amount

1234             1/3/2014           175

1234             2/4/2014           200

1234             3/6/2014           150

1234             4/3/2014           190

1234             5/3/2014            250

4567             1/1/2014            300

4567             3/1/2014            145

Output:

1234,175,200,150 (comma-delimited.  maximum of 3 per row)

1234,190,250

4567,300,145

Thanks.

7 REPLIES
Senior Apprentice

Re: Rows to Column (Column-delimited)

Create a row number and then use some MOD/DIV:

SELECT Account#,
COALESCE( TRIM(MIN(CASE WHEN rn MOD 3 = 0 THEN amount END)), '') ||
COALESCE(',' || TRIM(MIN(CASE WHEN rn MOD 3 = 1 THEN amount END)), '') ||
COALESCE(',' || TRIM(MIN(CASE WHEN rn MOD 3 = 2 THEN amount END)), '')
FROM
(
SELECT
Account#, amount,
ROW_NUMBER() OVER (PARTITION BY ACCOUNT# ORDER BY date) - 1 AS rn
FROM vt
) AS dt
GROUP BY Account#, rn/3
ORDER BY Account#, rn/3
Enthusiast

Re: Rows to Column (Column-delimited)

Dieter, thanks for your quick response.   Your solution worked 95%.  Do you know why the first row returned only one amount.  Example:  1234 ,175.  Next row, it put:  1234 200,150,190.  It could be something I am doing wrong.

Thanks.

Senior Apprentice

Re: Rows to Column (Column-delimited)

You will get this if you use ROW_NUMBER +1 instead of ROW_NUMBER -1

Enthusiast

Re: Rows to Column (Column-delimited)

Your code works.  It was me - I had a "where" condition outside the partition casuing the issue.  I put the "where" inside the select from VT and it worked fine.  Thanks.

Enthusiast

Re: Rows to Column (Column-delimited)

How can I get a  row number for each row?

Output:

0001,1234,175,200,150 (comma-delimited. maximum of 3 per row)

0002,1234,190,250

0003,4567,300,145

Senior Apprentice

Re: Rows to Column (Column-delimited)

Wrap the query in a Derived Table and add a ROW_NUMBER:

SELECT TRIM(ROW_NUMBER() OVER (ORDER BY ACCOUNT#, rn) (FORMAT'9(4)')) || ','
|| TRIM(ACCOUNT#) || ',' || txt
FROM
(
SELECT ACCOUNT#,
rn/3 + 1 AS rn,
COALESCE( TRIM(MIN(CASE WHEN rn MOD 3 = 0 THEN amount end)), '') ||
COALESCE(',' || TRIM(MIN(CASE WHEN rn MOD 3 = 1 THEN amount end)), '') ||
COALESCE(',' || TRIM(MIN(CASE WHEN rn MOD 3 = 2 THEN amount end)), '') AS txt
FROM
(
SELECT
ACCOUNT#, amount,
ROW_NUMBER() OVER (PARTITION BY ACCOUNT# ORDER BY date) - 1 AS rn
FROM vt
) AS dt
GROUP BY ACCOUNT#, rn/3 + 1
) AS dt
Enthusiast

Re: Rows to Column (Column-delimited)

Dieter, thanks for your help!!  It worked.