Group columns of a SQL query into an array

Question

I have the following SQL query:

SELECT Date AS timestamps, Seasons1 AS seasons_1, Seasons2 AS seasons_2,
    Seasons3 AS seasons_3, Seasons4 AS seasons_4, Seasons5 AS seasons_5,
    Seasons6 AS seasons_6, Seasons7 AS seasons_7
FROM SeasonsTable WITH(NOLOCK)
ORDER BY timestamps DESC

Is there any way to return a single column (called say seasons) that contains all the seasons_* as an array?

For example, this currently returns:

timestamps seasons_1 ... seasons_7
2019-04-28 1.0  2.0  3.0  4.0  5.0  6.0  7.0

but I'd like it to return:

timestamps seasons
2019-04-28 [1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0]

(1) Your code is not MySQL code. (2) SQL does not have an "array" type. — Gordon Linoff
– Gordon Linoff, Commented May 8, 2019 at 10:14
@GordonLinoff Thanks and I've updated the tags. A bit of a newbie here. — ajrlewis
– ajrlewis, Commented May 8, 2019 at 10:15
You are going to have to add the sql dialect you are using and the version. — P.Salmon
– P.Salmon, Commented May 8, 2019 at 10:17
@apomene I get Error converting data type varchar to float.. — ajrlewis
– ajrlewis, Commented May 8, 2019 at 10:19

iainc · Accepted Answer · 2019-05-08 10:27:58Z

1

As mentioned in the comments, TSQL doesn't have arrays. To return the data as a delimited string you just need to join the columns:

SELECT Date AS timestamps, Seasons1 + ',' + Seasons2 + ',' + Seasons3 + ',' + Seasons4 + ',' + Seasons5 + ',' +  Seasons6 + ',' + Seasons7 AS season
FROM SeasonsTable WITH(NOLOCK)
ORDER BY timestamps DESC

Dependent on the datatype of your seasons columns you might need to use cast(seasonsX as varchar) rather than just the column name.

answered May 8, 2019 at 10:27

iainc

8687 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fleshy · Accepted Answer · 2019-05-08 10:27:03Z

1

select Date as timestamps, '[' + cast(Seasons1 as nvarchar) + ',' + cast(Seasons2 as nvarchar) + ... + ']' as seasons

answered May 8, 2019 at 10:27

Fleshy

1827 bronze badges

Collectives™ on Stack Overflow

Group columns of a SQL query into an array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related