|
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.
|
|
|
|
|
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.
modified on Monday, December 27, 2010 2:51 AM
|
|
|
|
|
appreciated, that is what i need!
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
|
please help....
this query is not working.. please tell me ERROR
INSERT INTO test Values(TO_DATE('12/23/2010','dd/mm/yyyy'))
Thnaks In Advance
|
|
|
|
|
|
Where is it supposed to put it?
|
|
|
|
|
in advance, where ever that is.
|
|
|
|
|
You might want to check the SQL INSERT query syntax.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
What error are you getting? What's the underlying data type in this field? What's the table definition (your query only stands a chance of working if there's only a date field in the table test)?
|
|
|
|
|
i am Using SQL
Msg 195, Level 15, State 10, Line 1
'TO_DATE' is not a recognized built-in function name.
|
|
|
|
|
Are you using SQL Server? TO_DATE is Oracle syntax. The equivalent in SQL Server is CONVERT(datetime, '12/23/2010', 101)
|
|
|
|
|
Yes I am Using SQL Server 2005
and i need to insert date in dd/mm/yyyy format
how it possible
please help me
|
|
|
|
|
A few thoughts.
- Is this field actually a datetime?
- Is it a datetime in your application?
- What does the format of the date matter?
If the field is a datetime, and it's being passed in as a datetime then you don't need to do anything. Just insert it as it is - the only reason you'd convert it is if you were passing it in as a string, and you should really avoid this and pass it in as a datetime instead. The format of the date is immaterial - just store it in the universal date format; choosing the format is only relevant when you display the date to the user and you should use the users locale to do this.
|
|
|
|
|
mjawadkhatri wrote: please tell me ERROR
Actually you should tell us about that. what's the error message?
FYI Oracle to_date usage[^]
|
|
|
|
|
i am Using SQL 2005
Msg 195, Level 15, State 10, Line 1
'TO_DATE' is not a recognized built-in function name.
|
|
|
|
|
Don't worry, you mixed days and months. There is no 23. month since I know.
TO_DATE('12/23/2010', 'mm/dd/yyyy') or TO_DATE('23/12/2010', 'dd/mm/yyyy')
try it manually before using it: select TO_DATE('12/23/2010', 'mm/dd/yyyy') from dual;
Regards
Ralf
|
|
|
|
|
Your query is allright IF
a) You have only one column in table to insert
b) You have function called TO_DATE created to give you supposed output compatible with column you have
c) Your function taking two parameters as a INPUT.
mjawadkhatri wrote: this query is not working.. please tell me ERROR
Only SQL can give neither any of us, Sorry for that.
|
|
|
|
|
AND d) the parameters to to_date are consistent.
TO_DATE('12/23/2010','dd/mm/yyyy') is no good.
|
|
|
|
|
AND e) He's not using Oracle.
|
|
|
|
|
|
Maybe because of semicolon you have missed.
For your info : Oracle TO_DATE
modified 24-Apr-20 22:18pm.
|
|
|
|
|
i want to join two tables(Firms and People) to show them in datagridview with ascending order in my program side but the thing is that they dont have any common point.so can i join them ?
vemedya.com
|
|
|
|