|
yes I have but isn't exactly what I need ! thanx
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
