Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
real_pearl
Posting Yak Master
106 Posts |
 Posted - 2004-06-02 : 05:33:41
|
| If I have a table column containing 100,000 rows and now I want to change its rows into columns (cross-tab) in a dynamic SQL. How I can do this as maximum varchar length is 8000.Declare @sql varchar(8000)SELECT @sql=@sql + '''' + 'Bin'+convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivotThis generates good cross-tab but the only limitation is @sql can not contain more than 8000 characters. Please give me some solution |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-06-02 : 09:06:10
|
| how many columns would your cross tab end up with if it worked?- Jeff |
 |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-06-02 : 09:08:03
|
| Are you sure you want to do this ? Even if you could, it gives an almost meaningless number of columns.Is there a smaller distinct set of key values you could return with either a count or sum of the population in each cell ? |
|
|
|
real_pearl
Posting Yak Master
106 Posts |
Posted - 2004-06-02 : 09:50:28
|
| It pivot table can contain 50,000 or more columns because Bins are defined by the user. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-06-02 : 09:54:59
|
| what is the point of returning a recordset with 50,000 columns? what happens to this result? is it displayed to the user? are you printing a hard-copy? why are you cross-tabbing it?- Jeff |
|
|
|
real_pearl
Posting Yak Master
106 Posts |
Posted - 2004-06-02 : 09:58:14
|
| I will have to show each bin's total number of parts. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-06-02 : 10:10:13
|
show us what you'd like returned ... give us an example. I honestly have no idea what you are looking for....something like this?Bin Total Parts--- -----------A 102B 12C 12,000D 9,123 ???what do you need all the columns for? to show the actual # of parts by type, and there are 50,000 different possible types? Or can you summarize them into, say, 20 different part types, and do something like this:Bin Total Parts Type1 Type2 Type3 Type4--- ----------- ----- ----- ----- ----- ..etc...A 102 12 90B 12 1 9 2 C 12,000 11,000 500 500 D 9,123 3,022 3,000 3,99 in the above, even though there many thousand parts, it doesn't mean you need (or want to!) return many thousand columns or rows -- that's what cross-tabs are for: to summarize your data.this whole thing is much easier if you explain to us what you are trying to acheive.- Jeff |
|
|
|
|
|
|
|
|
Help needed in Cross-Tab