Transpose columns to rows with ANSI sql

Question

| id |
+----+
| 1  |
| 2  |
| 4  |
| 5  |
+----+

Output as row

1 2 4 5

Without use of Pivot method ,no hardcoding like checking with case with when 1 = 1.No dynamic Sql .with out any inbuilt function.

I have searched questions but could not find anything in pure sql Is there a way?

Possible duplicate of [Simple way to transpose columns and rows in Sql?](https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — René Vogt, Feb 01 '19 at 16:39

score 1 · Answer 1 · answered Feb 01 '19 at 16:41

Most SQL databases support some sort of group concatenation ability. For example, in MySQL we could use GROUP_CONCAT:

SELECT GROUP_CONCAT(id ORDER BY id SEPARATOR ' ') AS output
FROM yourTable;

The ANSI standard may not define anything, but SQL Server, Oracle, and Postgres, to name a few, can do something similar to the above.

Yogesh Sharma · Answer 2 · 2019-02-01T17:20:31.410

I would use correlated subquery & do conditional aggergation since some DBMS doesn't have a row_number() :

SELECT MAX(CASE WHEN Seq = 1 THEN Id END) AS ID1,
       . . . 
       MAX(CASE WHEN Seq = 4 THEN Id END) AS ID4
FROM (SELECT t.*,
             (SELECT COUNT(*) FROM table t1 WHERE t1.id <= t.id) AS Seq
      FROM table t
     ) t;

However, this method might fail if the ID is not in Sequential manner if so, then you would need to use PK (IDENTITY Column) that specify column ordering.

score 1 · Answer 3 · answered Feb 01 '19 at 17:34

Maybe your database supports the LISTAGG function of ANSI SQL:2016: It is a function to transform values from a group of rows into a delimited string.

See syntax as well as database support and alternatives here:

Listagg is an ordered set function, which require the within group clause to specify an order. The minimal syntax is: LISTAGG(<expression>, <separator>) WITHIN GROUP(ORDER BY …)

score 0 · Accepted Answer · answered Feb 01 '19 at 16:37

0

If you know you have four values, you can use conditional aggregation:

select max(case when seqnum = 1 then id end) as id_1,
       max(case when seqnum = 2 then id end) as id_2,
       max(case when seqnum = 3 then id end) as id_3,
       max(case when seqnum = 4 then id end) as id_4       
from (select t.*, row_number() over (order by id) as seqnum
      from t
     ) t;

I'm not sure if this fits your requirements. If you are looking for something with a variable number of columns, then you need to use dynamic SQL.

answered Feb 01 '19 at 16:37

Gordon Linoff

1,242,037
58
646
786

If i dont know the exact num of row for that col then dynamic sql is the option..? – Dot Net Dev 19 Feb 01 '19 at 17:37
@DotNetDev19 . . . If you want the values in separate columns. – Gordon Linoff Feb 01 '19 at 18:06

Transpose columns to rows with ANSI sql

4 Answers4