question

SSGC avatar image
SSGC asked

How To pivot this table?

I have a table like this: create table #temp (day date, name varchar(5), class1 int, class2 int) insert into #temp select '01/01/2013','ABC',10,20 union select '01/01/2013','BCD',20,30 union select '01/02/2013','ABC',20,30 union select '01/02/2013','BCD',10,30 union select '01/03/2013','BCD',20,20 day name class1 class2 2013-01-01 ABC 10 20 2013-01-01 BCD 20 30 2013-01-02 ABC 20 30 2013-01-02 BCD 10 30 2013-01-03 BCD 20 20 Now I want table like this: Date name1 class1 class2 name2 class1 class2 2013-01-01 ABC 10 20 BCD 20 30 2013-01-02 ABC 20 30 BCD 10 30 2013-01-03 BCD 20 20 Is it possible use PIVOT function? Is it possible in SSRS report? If someone can help me this I am very Appreciate.
ssrspivot
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What do you want done if more than two class names are present for a given date?
0 Likes 0 ·
Thanks to someone help me orgnaizing the question. I didn't know how to input T-SQL or data here. when we have more than two class names, we want it auto continue on the different columns. Thanks!
0 Likes 0 ·
Glad to help with the formatting. Hopefully my comment formatting turns out okay. Would this be a good set of test rows for more than two class names: select '01/04/2013','ABC',10,20 union select '01/04/2013','BCD',20,30 union select '01/04/2013','DRF',25,35 If so, when extending the results for the additional columns, do you want the column groups added alphabetically by the value of the name column? That seems to be how the sample result you provided is structured. Something like this with a 3rd name group: Date name1 class1 class2 name2 class1 class2 name3 class3 class3 2013-01-04 ABC 10 20 BCD 20 30 DRF 25 35 How about gaps in the horizontal data? Does the order need to be honored like in the sample output you gave. Starting to get a bit crazy with the layout, but thought I'd ask. Date name1 class1 class2 name2 class1 class2 name3 class3 class3 2013-01-01 ABC 10 20 BCD 20 30 2013-01-02 ABC 20 30 BCD 10 30 2013-01-03 BCD 10 20 2013-01-04 ABC 10 20 BCD 20 30 DRF 25 35 2013-01-05 ABC 10 20 DRF 25 35 How many columns would you actually expect? We've gone from 7 in the sample, to 10 in the comments. Could it be dozens of columns? hundreds?
0 Likes 0 ·
Thanks for the posting, The gaps we can leave it as blank or null if it is not all data are blank or null. Client want to see the each day is a row all other data is on the columns. for this case, We just have maximum 10 names. so maximum columns is 31. If possible can it show dynamiclly for the columns?
0 Likes 0 ·
@SSGC I have to ask. Does the original table actually look like what you posted as #Temp or is that after you already did some preprocessing on the real table? I ask because it's denormalized data and will require an extraordinary coding effort in most areas if you add just one more column (Class3, for example). If it's not actually what the original table looks like, please post that original table and we'll make short work of this problem. If the original table does actually look like what you've posted, then post back and we'll make equally short work of the problem without getting into dynamic code because dynamic code would be the least of your problems if you added a column.
0 Likes 0 ·
Show more comments

1 Answer

· Write an Answer
xiaoyuandlg avatar image
xiaoyuandlg Suspended answered
Don't know the exact way and feel faint about this question. But, sounds like something similar to a [pivot grid component][1] can make this. You may add something like this to simplify your problem. [1]: http://www.kettic.com/winforms_ui/pivotgrid.shtml
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.