CODEWITHSUNDEEP
sqlt-sqlsybase
behzad.nouri
behzad.nouri

Reputation: 78011

SQL: group by, order by and concat the results

Is there a functionality in sql which can perform this efficiently?

Figuratively, I have a database with three columns: tape_date, empl, and a single character column val; I want to extract the most recent 3 values of val for each empl concatenated together in the order of their tape_date. For example for the table below I should get a row

bob, DEA

and similarly for other empl's. The order matters, so for example EDA would be wrong result for bob.

I am using Sybase Adaptive Server Enterprise/15.7.0, but prefer not to use proprietary features.

tape_date    empl    val
------------------------
2014-01-08   bob     A
2014-01-01   bob     G
2014-01-03   alice   K
2014-01-02   bob     D
2014-01-05   bob     E

Upvotes: 3

Views: 225

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

There are several ways you could do this, one is to use the Sybase LIST function if you don't mind it only running on Sybase. If not then you can do it using RANK and MAX. Like so:

SELECT 
  CAST('2014-01-03' AS DATE) AS tape_date
  ,'alice' AS empl   
  ,'K' AS val
INTO #temp_table;
INSERT INTO #temp_table VALUES('2014-01-08', 'bob', 'A');
INSERT INTO #temp_table VALUES('2014-01-01', 'bob', 'G');
INSERT INTO #temp_table VALUES('2014-01-02', 'bob', 'D');
INSERT INTO #temp_table VALUES('2014-01-05', 'bob', 'E');


-- USING SYBASE ONLY LIST FUNCTION
SELECT empl, SUBSTR(LIST(val ORDER BY tape_date), 1, 5)
FROM #temp_table
GROUP BY empl;

-- THE GENERIC WAY
SELECT 
  empl
  ,MAX(CASE rank WHEN 1 THEN val ELSE '' END) 
    + ',' 
    + MAX(CASE rank WHEN 2 THEN val ELSE '' END)
    + ',' 
    + MAX(CASE rank WHEN 2 THEN val ELSE '' END)
FROM (
SELECT
  RANK() OVER (PARTITION BY empl ORDER BY tape_date) AS rank
  ,*  
FROM #temp_table) a
WHERE rank <= 3
GROUP BY 
  empl

Upvotes: 2

Lluis Martinez
Lluis Martinez

Reputation: 1973

The only way to "flatten" the rows returned by the group by query is by using a procedural language (T-SQL, C#, Java). Not possible in SQL alone.

Upvotes: 0

Related Questions