StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is too long for a comment.</p> <p>The maximum number of columns in a normal SQL Server table is 1,024 (see <a href="http://msdn.microsoft.com/en-us/library/ms143432%28v=sql.105%29.aspx">here</a>).</p> <p>Your request is dangerously close to the limit. So, although it is technically possible, it is highly not recommended. I'm not sure why Mahmoud deleted his answer, because that answer seems correct for this purpose.</p> <p>You <em>can</em> investigate something called "wide tables" which contain "sparse columns", if most of the values are 0/NULL.</p> <p>If you explain the reason why you want to do this pivot, then there might be other solutions to your problem.</p> <p>Oh, I have great sympathy for wanting to do data analysis with data in SQL. Here generic options:</p> <p>(1) Import the data into SAS, SPSS, or R. These do not have arbitrarily limits on the number of columns.</p> <p>(2) Concatenate the values together into a single column, perhaps separated by a comma. It can then be readily brought into another tool.</p> <p>(3) Use a database that has a higher limit on the number of columns, such as Oracle or MySQL (still have lowish limits but a bit better).</p> <p>(4) Use a technique called vertical partitioning to put the columns in separate tables. Supported directly by databases such as Vertica. Can be implemented by other databases. However, you then have the problem of the maximum number of columns allowed in a query.</p> <p>For your particular problem, I would strongly encourage you to investigate clustering algorithms. K-means probably isn't going to give very good results on such wide data. I don't know if SSAS supports EM clustering, which might be a bit better. You could try to transform the analysis into an association rule problem. But the serious approach woudl be to transform the data using principal components and do the clustering in a lower dimensional space.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload