Can We Interchange The Column Position In Sql
May 26, 2008HI All,
can we interchange the column position in sql.
ADVERTISEMENT
Column Position Sql Table
Jul 26, 2007 There are about 500 tables in one particular datbase. There are
foreign keys set on most of the tables. I want to change the position
of the primary key column in all those tables. How can I do that
programatically?
Swap Column Position
Jul 20, 2005What is a good method/mechanism to swap the position of multiplecolumns?For instance, tblXZY has the followings columns and respectivepositions:tblXZY======xyzUUID 1fn 2ln 3phone 4email 5city 6state 7....Now, I need to make city as 2 and state as 3. BTW, the tblXYZ tablehas data there. Copy/select all the data into a new table withdesired column position would require constraints re-mapping etc.,which seems quite a bit hassle.Thanks.
View 6 Replies View RelatedAdd Column In Specific Position
Dec 12, 2007What is the SQL for adding a column at specific location?
eg
TableA
colA
colB
I want to add colC after colA.
Thanks,
Max
Can I Move Some Column Position By Using SQL Command ?
Jun 1, 2007I created SQL table follow by XSD fileAnd when any users added new column to XSD in ordinal position = 3 But after my program successfully created new column, its position is the last position What can I do ???? I suspect why I can't set it (I've looked for solution on MSDN already)even though We can see ordinal position bythis query SELECT *FROM INFORMATION_SCHEMA.Columns What can I do for solving ???? Help me please
View 1 Replies View RelatedTSQL Change The Position Of A Column?
Dec 16, 2004We have a little app to help us move applications from developent --> stage --> production. It uploads the files, runs the new stored procedures etc.
One of the things it does is create new columns in existing tables. In EM you can create a new column in any position, but ALTER TABLE seems to only add columns to the end of the table. Is there a way in TSQL to create columns in arbitrary positions in the table? Or do I have to recreate the whole table?
Change Ordinal Position Of The Column
Jun 18, 20081 have a table with 74 colun.But due to some deletion i have the ordinal postions as
1
2
3
.
.
69
70
72
73
74
76
what i wants is to change the ordinal from 72 to 71,73 to 72,74 to 73,76 to 74
SELECT @field = 0, @maxfield = max(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
-- handle insert case here
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.
I needed it as i have a trigger whihc is adding value in a log table on update but due to this problem it fails for certain column
Trgger is solme thing like
WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1
--IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('I','D')
IF substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in ('D') -- For the insertion case don't save the iserted data.
BEGIN
IF @Type not in ('I')
BEGIN
SELECT @fieldname = COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName and ORDINAL_POSITION = @field -1
--print('fieldname = '+@fieldname)
SELECT @sql = 'insert Audit (ActionTypeID, RowID, TableName, PK, FieldName, OldValue, NewValue, UpdateDate,UserID, UserName, UserType)'
SELECT @sql = @sql + ' select convert(bigint,' + @ActionTypeID + ')'
SELECT @sql = @sql + ',' + @RowID
SELECT @sql = @sql + ',''' + @TableName + ''''
SELECT @sql = @sql + ',' + @PKSelect
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ',' + @UserID
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ',''' + @UserType + ''''
SELECT @sql = @sql + ' from #ins i full outer join #del d'
SELECT @sql = @sql + @PKCols
SELECT @sql = @sql + ' where i.' + @fieldname + ' <> d.' + @fieldname
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is null and d.' + @fieldname + ' is not null)'
SELECT @sql = @sql + ' or (i.' + @fieldname + ' is not null and d.' + @fieldname + ' is null)'
--print('@sq=====sdfdfsfsdf')--sha
--print(@sql)--sha
EXEC (@sql)
END
END
END
Kamran Shahid
Sr. Software Engineer(MCSD.Net)
www.netprosys.com
Transact SQL :: Select Column By Ordinal Position
Apr 16, 2012Is there any way to select columns by ordinal position rather than by name?
Something like
Select t.[1] as col1, t.[2] as col2
FROM table t
The question comes because of a very specific situation where I've got 2 databases that have exactly the same schema at a column/datatype level but the column names are different. There are a few other ways to skin the cat but I'm interested if it can be done this way - without a join to syscolumns.
Any Function That Returns The Position Of A String In A Column?
Dec 12, 2007Hello!
Is there a function that gets the name of a column and a string as arguments and returns the position of this string in the column given?
Thank you in advance.
How To Interchange Two Row Values In Sql Server?
Aug 23, 2007Hi all!
how to interchange two row values?
ID NAME
1 DotNet
2 Java
3 DotNet
4 Java
this s the sample table..
here i want to interchange java to dotnet and vice versa.
rajesh
Stacked Column Chart Label Position Problem
Feb 26, 2008I have a Stacked Column graph and when we set the Position of the Point Label to be "Top Center", the Point Label always stays in the center of that particular Bar.
On a easier, basic Bar chart, the Point Label setting works correctly.
Has anyone else had a similar issue or know if this is a known issue ?
Getting Data Based On Ordinal Position Of The Column In The Table
Aug 30, 2007Hi
I am having a problem in auditing the column data in tables.My requirement is i have write a trigger which is capable of auditing the columns which are going to be added in the future also with out using dynamic SQL.is there any way to do so.
I feel if i can get the column data based on ordinal position then it is possible.
Can any body suggest.
My set Up is like this
I have a base_table to be audited.
I have a Audit_spec table which contains name of the table and columns to be audited.
And Audit table which actually captures the table name,column name ,old value and new value.
I have to audit only those columns in the Audit_spec spec.
If schema changes(Like new column added) happens to base_table and I want that column to be audited.with out any changes to my trigger code i should handle the newly added column ..
There Is No Row At Position 0
Jan 26, 2004hello everyone,
we have a problem in my system that will say it now.
we have two departments can access to the same database , the first department fill all the fields in the application(web_enabled), the second department can fill some fields from whole the fields and the remaining fields are set as default values because they don't have access to the remaining items.
the problem here, when the first department go to fill the fields and then press save to save the filled information , will operation successful, but when the second one want to fill the fields then click save , will appear this error
" there is no row at position 0".
note that we can't to change any thing in the code , i hope to find the answer for our problem thru database.
with my regards
How To Get The Position Of A Value?
Apr 11, 2007Hi,
I'm trying to get the position of a single value in the query results.
like this:
select * from tPerson order by ds_alias
results in
id_person | ds_name | ds_alias
15 | mark | AA
20 | john | AA
5 | mike | BB
8 | thomas | JK
2 | mike | MM
as you can see, I can have registries with the same name and with the same alias, there's no restriction
and I'm trying to find out how many registries there are before the second "mike", wich would be 3.. so it's the 4th element, get it?
I have no idea how to...
Thank in advance
Week Position
Jun 21, 2004Hi,
Is there an easy way to get the position (or number) of the week from a date? By position, I mean week number 1 if we are january 1st. For example:
Date | Week postion
=====================
2001-01-05 | 1
1996-12-28 | 52
1987-06-15 | 26
Thanks a lot,
Skip.
Field By Position
Feb 7, 2005Hi, Nobody have idea to get a field in "select" statement by its position (without name then)?
My scope was to duplicate a row into a table with identity column getting all row except the identity one, that i want to provided by constant expression.
Bye and thanks...
:D :D
Ordinal Position
Jul 20, 2005Is there a neat way to find an ordinal value from a table,for example the median or 95th percentile value in a column,without walking through the table in ascending or descendingorder?Thanks,Jim GeissmanCountrywide Home Loans
View 1 Replies View RelatedThe Position Of The Package Run
Oct 19, 2007
When i use the "run package task" to run a package in the sqlserver storage,the package will run at the storage server or the server call this package?
Thanks!
Current Position Of A ROW In A Table?
Nov 16, 2001Hi Everybody,
Is there any way to find the current position of a row in a table?. Like what we have in Oracle ROWID, Like that anything available in SQL Server.
I have seen NEWID(),uniqueidentifier & ROWGUIDCOL in BOL. Apart from that any easy method is available in SQL Server?
thanks in advance,
Vasu
Retriving Position In A Field
Aug 5, 2004Hi All.
Is there a way to retrieve the position of a word, phrase or sign in a field?
For example, Field content is ABCDEFG1239/1002STJ
I would like to get the exact position of / which will be position 12.
Thank you.
Best regards
How To Get String Value In A Middle Position?
Feb 26, 2007khosara writes "I have one parameter @String with value "My name is Khosara".
How to get the value only "Is khos".
Could you please help me, witch method shold i use.
Thank in advance."
Position Of Table Columns
Jan 2, 2008Dear All,I want to get more than one table columns as a single column byalternative.For eg,Table name = employeecolumns = empid, empname, address1,address2Here, I want to interpolate the address2 column on address1 asmentioned below,empid empname address1address21 aaa First Layout,CA, US.Thanks in advance.Thanks and Regards,Ganapathi sundaram.G
View 3 Replies View RelatedDTS Select Using Record Position
Jul 20, 2005Hi there, it has been a while since i have posted. I am in asituation where I am stumped. I am learning to build a dts packagewhere I am connecting to a table in an AS400. This database is beingmaintained by an outsourced company and therefore I can't change thetable structure or even ask them to. Anyway, this table currently hasabout 104,000 records. I am building a package to check it and pullout the most recent records and put them where they go in my SQLServer 2000 tables. The only way I can think of to get the mostrecent records is to use a global variable in the package to rememberthe record count and then get those records from that record positionon. Problem is, I have no idea how I would go about selecting recordsfrom a record position. Does anybody have any ideas or should I beusing a different approach? There are no time stamps to work from. Iwas told that the AS400 records, including updates, are appended tothe table, which is why I thought this approach made sense. I wouldtruly appreciate any help.
View 1 Replies View RelatedThere Is No Data For The Field At Position XX
Jan 25, 2008I am so frustrated, I don't know what to do. I have spoken to everyone that I know on how to explain this error message, but my supervisor wants to dispute every thing I tell him.
Could someone PLEASE explain the following error message and how I can go about finding what fields that it's talking about?
e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 15., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 15.
I have two environments (2 seperate companies on seperate servers). In Environment #1 (referred to as Env1 from this point on) that runs the report and generates a few of these errors (10 for an example). In Environment #2, the same report only modified SLIGHTLY for the other company, runs the report and generates 50 errors. I'd be more than happy to post the log here if someone needs it.
Someone, please, save me from logfile hell. I have told this supervisor that "No data for the field" pretty much means that there was NO DATA RETURNED. We know for a FACT that these two environments are completely different. One runs on real servers and the other is running on partial real servers and virtual servers. We have identified several differences in the configurations, but he wants me to nail down EXACTLY what is causing this error. Please remember, "No Data" is not a good enough response for this person.
Please, for the love of all that is Chocolate, HELP!!!!!!!!!!!!!!!
Thanks in advance,
Jim Evans
Microsoft Certified Application Developer
Report Parameter Position
Apr 30, 2008
Hi All,
I have a parameter which populates the years. when user selects the year, i populate the start period values and end period values in two other parameters. So the the report has 3 parameters for the report.
The look and feel of the parameters is as follows:
First row : Year Parameter and Start Period Parameter .
Second row : End Period Parameter
But I want the parameter positions or look & feel as follows:
First row : Year Parameter .
Second row : Start Period Parameter & End Period Parameter.
Can anyone give a suggestion or hack or tip for my requirement.
Thanks,
Madhu.
Using An Expression To Set The Position Of A Subreport.
May 2, 2008
Dear all,
I feel I am almost there with my current project but I am just trying to solve the last few problems here.
I was not able to have a columnized sub report within my main non columnized report as all sub reports inherit the layout of the main page (a known limitation).
However, I need to get some data into columns on my main page so I have had to hack around the issue to get the desired results.
Instead of creating one columnized subreport (which wont work in the main page) I have added two non-columnized sub reports to the main page listing the first half of the data in one subreport and the 2nd half of the data in the other subreport.
The problem is this. I need to put a total at the end of the data. And it depends how much data there is as to where the total should be (left column or right column) If there are only 10 lines of data then the first left hand sub report will display those results and the right hand sub report wont display anything. However, if there are 100 lines returned then both sub reports will display data and the total will need to be at the bottom of the right sub report.
Can I achieve this with an IIF statement? Any suggestions would be most helpful.
Trevor Keast
Nth Pattern Position Of The String
Mar 12, 2008
Hi,
How can i find the 2nd comma position of a string, if i have multiple commas in my field. I need SQL Statement
Ex:
F1
Andy,David,Martin,
Sam,Dan,Philip,
I need the position should be
F1 F2
Andy,David,Martin, 11
Sam,Dan,Philip, 8
Thanks,
Mears
Absolute Object Position
Jul 16, 2007I have a table that expands and pushes down objects under it, i don't want this to happen i want my objects to use an absolute position so i can jiggle things how i want them - this doesn't seem to be an option in SSRS 2005?
View 2 Replies View RelatedAbsolute Position On Page
Jul 17, 2007when i put a table beside 2 graphs on my report, as the table expands it pushs the bottom table down ONLY when when it's displayed on the final webpage. the preview features shows it as being correct.
how do i prevent the expanding table from pushing the tables BESIDE it down? i could understand if it was a table below it, but it's not.
Implementing Ranking Position
Jan 17, 2007Hello,
I have a group that is custom made on the table's grouping expression, something like
=SWITCH(Fields!Country.Value="Portugal",IIF(Fields!Storetype.Value="Frs","Portugal Frs", "Portugal"),
Fields!Country.Value="Italy","Italy",Fields!Country.Value="Spain","Spain",Fields!Country.Value="United States","United States",
Fields!Country.Value="Internet","Internet",Fields!Country.Value<>"","Other")
The thing is that i order this by my value, and then i use a column that is the rowcount, but sadly i got
11 Portugal
19 United States
30 Spain
How can i do this like
1 Portugal
2 US
3 Spain
Is there something like in C ++1 or something that increments a number depending on the line of the table that is in?
Thank you
Autoincrement Record Position In A View
Nov 13, 2003Does anybody know the function or any other way in MS SQL Server 2000 or in MS Access or in MS FoxPro that I can get an increment record position in a view?
For example let’s say that I have a table with only one field named persons. The table has three records person1, person2 and person3. What is the way in MS SQL Server 2000 or in MS Access or in MS FoxPro of retrieving the records in a view with an extra field named for example recno which will indicate the record autoincrement number in the view as it is below?
recno persons
1person1
2person2
3person3
Please help me
I will be very grateful if you also reply your answers also and to my email
Email: stavrinc@hotmail.com
Thank you
Christos Stavrinou
Inserting Columns To A Table At A Particular Position
Jun 26, 2001Hi,
Does anyone know if it is possible to add a column to a table at a particular position. The ALTER TABLE statement specifies that an ADD COLUMN puts this on the end of the table. Is there a way to insert into the middle of the table?
Alternatively, can it be done through SQL-DMO?
Obviously the fallback would be to drop and re-create the table with the correct column ordering, however this is not ideal for tables with large amounts of data.
It's possible to do this through Enterprise Manager (using Insert Column), so how does it do it - DMO, or by dropping and re-creating?
We're building a database schema upgrage tool and are trying to cover all possibilities.
Thanks,
Steve Jenkins.
Selecting Rows By There Position In The Table
Jan 25, 2001Hy!
I'm trying to write a query which returns only some rows in my table...
For example :
I want all the rows included between 30 and 50 from table XYZ
(30 and 50 are not keys but really the numbers of records)
Is there someone out there who has a hint for me???
Thank you for your help and sorry for my english!
RadiFluide