Transpose in SQL 2k5
-
Dear All, I have bellow table which I want to transpose, the pivot in SQL 2k5 works but I want it like in a dynamic way. as lets say i have table EmpID Name 1 A 2 B I want to transpose by EmpID, that works fine but what if one more record is added to the table as EmpID Name 1 A 2 B 3 C then my transpose only picks up 1 and 2, how can i dynamically transpose that?
Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan
-
Dear All, I have bellow table which I want to transpose, the pivot in SQL 2k5 works but I want it like in a dynamic way. as lets say i have table EmpID Name 1 A 2 B I want to transpose by EmpID, that works fine but what if one more record is added to the table as EmpID Name 1 A 2 B 3 C then my transpose only picks up 1 and 2, how can i dynamically transpose that?
Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan
I do that in a DataTable once I've read the data, you could also do it while reading the data. I've never had a need for doing that in the database.
-
Dear All, I have bellow table which I want to transpose, the pivot in SQL 2k5 works but I want it like in a dynamic way. as lets say i have table EmpID Name 1 A 2 B I want to transpose by EmpID, that works fine but what if one more record is added to the table as EmpID Name 1 A 2 B 3 C then my transpose only picks up 1 and 2, how can i dynamically transpose that?
Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan
I hope you are asking about dynamic column generation with PIVOT. You can first generate all the column dynamically using SQL Query and then you can give as a column input to PIVOT that will work beyond the static column bounary. You could find THIS[^] to be useful.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator.modified on Monday, December 27, 2010 2:51 AM
-
I do that in a DataTable once I've read the data, you could also do it while reading the data. I've never had a need for doing that in the database.
thanks for your reply, doing in DataTable is the simplest solution.
Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan
-
I hope you are asking about dynamic column generation with PIVOT. You can first generate all the column dynamically using SQL Query and then you can give as a column input to PIVOT that will work beyond the static column bounary. You could find THIS[^] to be useful.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator.modified on Monday, December 27, 2010 2:51 AM
appreciated, that is what i need!
Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan
-
appreciated, that is what i need!
Abdul Rahaman Hamidy Database Developer Kabul, Afghanistan
Glad it helped you.
Regards, Hiren.
My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - The ?? Operator.