SQL Server: Select and max number of columns can be used there

Question

Scenario 1

out of curiosity i am asking a question. i heard sql server columns per table is 1,024

suppose i am joining 4 tables and 4 tables has 500 columns. can i refer more than 1500 columns in sql server?

if not possible then what work around solution exist to handle this kind of situation

Scenario 2

---

suppose i have sample table here like

Section Lineitem Periods

these 3 are columns of the table. periods table has periods like 1Q 2010, 2Q 2010, 3Q 2010, 4Q 2010, 2010 FYA............2099 FYE

i want to show the period horizontally using Pivot functionality. if number of dynamic columns could be more than 1,024 then select will be able to show data.

if my questions are not clear please let me know.

please guide me. thanks

Answer 1

Hi @T.Zacks ,

can i refer more than 1500 columns in sql server?

As mentioned by Erland, the maximun number of columns is 4096 per SELECT statement.

periods table has periods like 1Q 2010, 2Q 2010, 3Q 2010, 4Q 2010, 2010 FYA............2099 FYE

I counted above and found that there were around (2099-2010)*5=445 columns needed in your dynamic pivot. It is enough since it is smaller than 4096.

I also created one table with 1050 rows and could perform the pivot function successfully with more than 1024 columns in the result from my side.

If you have more than 4096/89=46 columns per year or have more years beyond 2099, then you have to find a second solution. For example, you could split the years into batches or find other solutions in other side.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

See Maximum capacity specifications for SQL Server. You can have up to 4096 columns in a SELECT statement.

If you need more than 1024 columns in your dynamic pivot, it's time to give a second thought of what you are doing. Maybe you should do the pivoting client-side after all, if so.