Dynamic Column Names In Select Statement
I have a quick question on SQL Server. Lets say I have table Order which has column names OrderId, CustomerName, OrderDate and NumberofItems. To select the OrderID values from the table I say
Select OrderId from Order.
But in the select if I want the column name to be variable how do I do it. I tried the following code through a stored procedure.
declare @order_id nvarchar(10)
select @order_id = 'OrderID'
SELECT @order_id from Order.
The code above gave me the string "OrderID" as many times as there were rows in the table but I could never get the actuall values in the OrderId column. Can you please send me some ideas or code where I can get values from the column names and at the same time change the column name dynamically.
View Complete Forum Thread with Replies
Related Forum Messages:
SELECT Statement - How To Not Get Column Field Names?
I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>
View Replies !
Looping Insert Statement With Dynamic Var Names...help...
672.1 I have a form that's sending 5 values, from form fields named "option1","option2" etc., to my SQL Server 7 DB. I want to loop through and insert each one into the table in my stored procedure. So I'm starting with: CREATE proc usp_upd_question @question_ID int output, @option1 varchar(255), @option2 varchar(255), @option3 varchar(255), @option4 varchar(255), @option5 varchar(255) as set nocount on declare @counter integer set @counter=1 while (@counter < 6) begin insert into question_options (question_ID, option_text) values (@question_ID, HERE'S MY PROBLEM!) set @counter=@counter+1 end Basically, I need some way to send the var ("option"+@counter) into the insert. I can get that string no problem, like set @thisoption='option'+cast(@counter as varchar(5)) ...but then it's a string, and I can't figure out how to get the DB to take that strong and look at it as though it were a variable. Anyone got any bright ideas? i'm sort of new at this SQL stuff. Thanks, Al
View Replies !
Dynamic Column Names In A SP
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View Replies !
Dynamic Column Names
Hi is possible to create dynamic column name example Declare @StartDate as dateTime Select @StartDate = '2004-06-05' select SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)], SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)] from table1
View Replies !
Dynamic Column Names
thanx for the help in advance, i have a table with Monday, Tuesday, Wednesday. .... Sunday i get the weekday by select @stat = datename(dw, getdate()) which in this case is Wednesday but when i do something like select top 2 * from myTable where 'myTable.'+@stat = 1 which is as same as select top 2 * from myTable where myTable.wednesday = 1 i receive a syntax error. how can i dynamically select myTable.Wednesday ? i also tried using select top 2 * from Intercon where '@stat' = '1' but then this doesn't return anything where it should. any help?
View Replies !
Dynamic Column Names
Hi guys 'n gals, I am trying to achieve something a bit complex (or at least appears to be for me). I have the following table structure: UID, GroupID, ColumnName, ColumnValue here is some example data: UID, GroupId, ColumnName, ColumnValue 1, 1, MAC Address, 8a7sd87sad 2, 1, IP Address, 192.168.1.100 3, 1, Name, John 4, 2, MAC Address, 09a8sd098as 5, 2, Name, Steven and here is what I would like the example to come out like: GroupID, MAC Address, IP Address, Name 1, 8a7sd87sad, 192.168.1.100, John 2, 09a8sd098as, NULL, Steven It needs to be completely dynamic though, as a new column name could be entered to the source table at any time... I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this: Code Snippet DECLARE @ColumnName varchar(128) DECLARE @Sql varchar(255) SELECT @ColumnName = [ColumnName] FROM tblSourceData SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData' EXEC (@Sql) Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go? Regards, Justin
View Replies !
Dynamic Column Names
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats. Mike "oh, that monkey is going to pay"
View Replies !
Dynamic Header Column Names In Excel
Folks, I am running into an issue while trying to export data to a spreadsheet. I actually don't know how to do it... Considering I only know the column names by the time I execute my procedure, I can't use the Excel Destination to export data. With DTSs I would create an ActiveX script to execute the procedure which loads the results into a temp table. After that I would select everything from this temp table and load the results into a record set, looping through this record set to create the destination spreadsheet with the dynamic column names. When it comes to SSIS we are advised to write vb.net scripts instead of ActiveX... These ones do not have records set's but dataset's, which at first glance are only applicable to xml and not xls files (when I try to define a variable as a dataset in my vb.net code, I face a message saying: Missing reference required to assembly System.Xml...). How I would create this spreadsheet using a vb.net code in SSIS packages? Please, help... Thank you.
View Replies !
Using Variables To Select Column Names
Hi I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example eg DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs' Select * FROM table1 WHERE @column = @value It keeps returning no results even though i've tried Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results I realise its the column which is not being selected, but there must be a way by using a variable? thanks
View Replies !
Dynamic Select/Update Statement Possible?
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter? For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following... ALTER PROCEDURE u_sp_x @semester int AS Select Semester@semester From ThisTable Just curious. Thanks, Steve Hanzelman
View Replies !
Dynamic Create Table Statement Or SELECT INTO
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind? Example: I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query. I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database. BTW - this would be a stored procedure... Any suggestions would be greatly appreciated.
View Replies !
Dynamic CREATE TABLE Or SELECT INTO Statement
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind? Example: I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query. I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database. BTW - this would be a stored procedure... Any suggestions would be greatly appreciated.
View Replies !
Dynamic Column Bind (Select * From..)
I'm looking for a way to use SSRS to display the contents of a returned dataset with 1-n columns. The number of columns is unknown at design time and the datasetview is using a stored procedure with 1 parameter to return the data. Does any one know if RDL supports wildcard(*) characters for field names or if there is another method. I have this working via a web based custom aspx page but it would be VERY helpful to utilize all the document conversion features the SSRS reports provide via the web. Thanks for any help. IT Publisher
View Replies !
Dynamic Select Statement Using Parameterized SqlCommand Or Multiple Possible .CommandTexts
I'm trying to create an interface for filtering profiles from an SQLServer 2005 database using an html form. The form allows filtering based on a minimum level required in between one and four different columns. The first (and only mandatory) column to be filtered on has its name hard-coded into the base query. In trying to get the other three possible criteria to work, I've taken several approaches, all of which failed.The other three potential criteria are selected from a drop down menu on the form and ideally these choices are passed into a query to be used as column names. My first attempt looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" .... if ReqSkill1 <> "" then level1 = Convert.ToInt32(Request.form("minskilllvl1")) query = query & pickclmleft & ReqSkill1 & pickclmright1 cmd.Parameters.Add("@ReqSkill1", SqlDBtype.text) cmd.Parameters("@ReqSkill1").value = ReqSkill1 cmd.Parameters.Add("@level1", SqlDBtype.int) cmd.Parameters("@level1").value = level1 end if above If statement was repeated for 2nd and 3rd optionsSecond approach was to remove all parameters from sections of the query that were appended onto the original statement. This involved lots of strings containing AND clauses with hard-coded column names which were appended on when the corresponding option was selected in the form. Code looked like this: query = "SELECT * FROM profiles_tbl WHERE (EngSkill >= @english)" ASPqry = " AND (ASPlevel >= " try con = new SqlConnection() con.ConnectionString = “**************string was correct****************� cmd = new SqlCommand() cmd.Parameters.Add("@english", SqlDBtype.int) cmd.Parameters("@english").value = english if ReqSkill1 <> "" then if ReqSkill1 = "ASPlevel" then query = query + " AND (ASPlevel >= " level1 = Convert.ToInt32(Request.form("minskilllvl1")) if level1 = 0 then query = query + "0)" end if if level1 = 1 then query = query + "1)" end if if level1 = 2 then query = query + "2)" end if if level1 = 3 then query = query + "3)" end if end if end ifFinally when this too failed, I created four entirely separate queries, detected how many criteria were used, and used the appropriate query, passing necessary skill level in as a parameter. I'll provide code if needed here. Queries were written as strings and then used to set the CommandText property for an SqlCommand variable. I think it's important to note that in all cases the most basic version of the query worked. In the first, if only the first criteria was used the statement executed fine. Same in the second. In the third, whatever query could be assigned first (even though only one could be assigned because of logical structure of if statements) worked and none of the others would. This last case was tested even with completely hard-coded queries that SQL Server 2005 validated as correct and would run. Any help is greatly appreciated. Will post as much code as people want/need, and if I can get any one of these methods working I'll be thrilled. I have no need for all three. A.S. Moser
View Replies !
Column With Select Statement
Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count? Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.
View Replies !
Possible To Parse A Column In A Select Statement?
I have a column called SEGMENTED_BLOCK sample data:X,X,XXX,XX,XX,TYZC123456,X,X,TOYZ654321,1234,777777I need to do something that has the effect ofSELECT(stuff before first comma) as FIRST_ITEM,(stuff after first comma, but before second) as NEXT_ITEM,(stuff after second comma but before third(if any)) as THIRD_ITEMFROM SEGMENT_XREFWHERE LOOKUP_ITEM = 12345ORDER BY FIRST_ITEMFIRST_ITEM is pretty easy, but it gets uglier fast.My attempts are horrendously ugly nested checkindex and substring statements.Is there an easier way?
View Replies !
Return SP In A SELECT Statement Column
I have a stored procedure which contains a complex scripting that is not an option to rewrite as a single SELECT statement. I want the following output: CatID | CatTitle | CatTree 001 | News | exec sp_DisplayTree(@CatID) My code I tried doesn't work: SELECT C.CatID As CatID, C.CatTitle As CatTitle, CatTree = (exec sp_DisplayTree C.CatID) FROM Cats As C WITH (nolock) I cannot find a solution to my solution, please help...
View Replies !
Select Statement Using Column Numbers
I have a number of lookup tables in my db. Each table typically has an ID column and a 'lookup values' column. I want to create a scalar function wherein, by supplying the table name and the ID, I want to retrieve the lookup value and vice-versa. I donot want to create seperate function for each table, which is easy. One function has to return values given the table name and the value of the ID or lookup value. The problem being faced is how to construct the select statement. An Execute statement created out of column names, retrieved with Col_Name(), is not useful as Execute statement is not permitted inside a a function.
View Replies !
Adding Column Values Together In SQL SELECT Statement
I have an SQL Select statement that I need to add a column to called SalePrice, the SalePrice column needs to be calculated by adding together the values of 12 columns, then multiplying that value by the value in a another column to calculate margin. My issue is that I can only get it to add 7 column values together, if I add any more columns to the equation it just returns and null result. My DB is SQL 2005 Express SP2. My select statement is below: SELECT dbo.MFG_DATA_Machines.ID, dbo.MFG_DATA_Machines.MachineName, dbo.MFG_DATA_Parts_CPU.PartDescription AS CPU, dbo.MFG_DATA_Parts_CPU.PartCost AS CPUCost, dbo.MFG_DATA_Parts_Motherboard.PartDescription AS Motherboard, dbo.MFG_DATA_Parts_Motherboard.PartCost AS MotherboardCost, dbo.MFG_DATA_Parts_RAM.PartDescription AS RAM, dbo.MFG_DATA_Parts_RAM.PartCost AS RAMCost, dbo.MFG_DATA_Parts_HDD.PartDescription AS HDD, dbo.MFG_DATA_Parts_HDD.PartCost AS HDDCost, dbo.MFG_DATA_Parts_OpticalDrive.PartDescription AS OpticalDrive, dbo.MFG_DATA_Parts_OpticalDrive.PartCost AS OpticalDriveCost, dbo.MFG_DATA_Parts_Video.PartDescription AS Video, dbo.MFG_DATA_Parts_Video.PartCost AS VideoCost, dbo.MFG_DATA_Parts_OS.PartDescription AS OS, dbo.MFG_DATA_Parts_OS.PartCost AS OSCost, dbo.MFG_DATA_Parts_Modem.PartDescription AS Modem, dbo.MFG_DATA_Parts_Modem.PartCost AS ModemCost, dbo.MFG_DATA_Parts_FloppyDrive.PartDescription AS FloppyDrive, dbo.MFG_DATA_Parts_FloppyDrive.PartCost AS FloppyDriveCost, dbo.MFG_DATA_Parts_CardReader.PartDescription AS CardReader, dbo.MFG_DATA_Parts_CardReader.PartCost AS CardReaderCost, dbo.MFG_DATA_Parts_PowerSupply.PartDescription AS PowerSupply, dbo.MFG_DATA_Parts_PowerSupply.PartCost AS PowerSupplyCost, dbo.MFG_DATA_Parts_CaseType.PartDescription AS CaseType, dbo.MFG_DATA_Parts_CaseType.PartCost AS CaseTypeCost, dbo.MFG_DATA_Machines.Notes, dbo.MFG_DATA_Machines.MarginPercent, dbo.MFG_DATA_Machines.PriceOverride, (dbo.MFG_DATA_Parts_CPU.PartCost + dbo.MFG_DATA_Parts_Motherboard.PartCost + dbo.MFG_DATA_Parts_RAM.PartCost + dbo.MFG_DATA_Parts_HDD.PartCost + dbo.MFG_DATA_Parts_OpticalDrive.PartCost + dbo.MFG_DATA_Parts_Video.PartCost + dbo.MFG_DATA_Parts_OS.PartCost + dbo.MFG_DATA_Parts_Modem.PartCost + dbo.MFG_DATA_Parts_FloppyDrive.PartCost + dbo.MFG_DATA_Parts_CardReader.PartCost + dbo.MFG_DATA_Parts_PowerSupply.PartCost + dbo.MFG_DATA_Parts_CaseType.PartCost) * ((dbo.MFG_DATA_Machines.MarginPercent + 100) / 100) AS SalePrice FROM dbo.MFG_DATA_Machines LEFT OUTER JOIN dbo.MFG_DATA_Parts_CaseType ON dbo.MFG_DATA_Machines.CaseType = dbo.MFG_DATA_Parts_CaseType.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Motherboard ON dbo.MFG_DATA_Machines.Motherboard = dbo.MFG_DATA_Parts_Motherboard.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Video ON dbo.MFG_DATA_Machines.Video = dbo.MFG_DATA_Parts_Video.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_RAM ON dbo.MFG_DATA_Machines.RAM = dbo.MFG_DATA_Parts_RAM.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_PowerSupply ON dbo.MFG_DATA_Machines.PowerSupply = dbo.MFG_DATA_Parts_PowerSupply.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OS ON dbo.MFG_DATA_Machines.OS = dbo.MFG_DATA_Parts_OS.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_OpticalDrive ON dbo.MFG_DATA_Machines.OpticalDrive = dbo.MFG_DATA_Parts_OpticalDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_Modem ON dbo.MFG_DATA_Machines.Modem = dbo.MFG_DATA_Parts_Modem.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_HDD ON dbo.MFG_DATA_Machines.HardDisk = dbo.MFG_DATA_Parts_HDD.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_FloppyDrive ON dbo.MFG_DATA_Machines.FloppyDrive = dbo.MFG_DATA_Parts_FloppyDrive.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CPU ON dbo.MFG_DATA_Machines.CPU = dbo.MFG_DATA_Parts_CPU.ID LEFT OUTER JOIN dbo.MFG_DATA_Parts_CardReader ON dbo.MFG_DATA_Machines.CardReader = dbo.MFG_DATA_Parts_CardReader.ID
View Replies !
How Do I Get The Actual Name Of A Column Or Table In A Sql Select Statement?
Hello fellow .net developers, In a website I'm working on I need to be able to put all of the user tables in a database in a dropdownlist. Another dropdownlist then will autopopulate itself with the names of all the columns from the table selected in the first dropdownlist. So, what I need to know is: is there a sql statement that can return this type of information? Example: Table Names in Database: Customers, Suppliers Columns in Customers Table: Name, Phone, Email, Address I click on the word "Customers" in the first dropdownlist. I then see the words "Name", "Phone", "Email", "Address" in the second dropdownlist. I'm sure you all know this (but I'll say it anyways): I could hardcode this stuff in my code behind file, but that would be really annoying and if the table structure changes I would have to revise my code on the webpage. So any ideas on how to do this the right way would be really cool. Thanks in advance, Robert
View Replies !
Concatenating Column Values In SELECT Statement
I'm puzzled as to how to express what I want in a stored procedure. Assume two columns, Surname and GivenName. The surname might be missing. When I originally wrote this app in Access, I used the following expression: SELECT Iif( IsNull(Surname), GivenName, Surname + ", " + GivenName ) AS Agent FROM Agents; I've looked at the syntax for CASE but I can't figure out exactly how to say what I intend, particularly the AS Agent column aliasing. Any help greatly appreciated. Please cc me privately so I receive your assistance at once! TIA, Arthur
View Replies !
How To Eliminate Column Header From Select Statement
Hi Everybody: I want select data from SQL server and save to a file without column header. Anyone has any suggestions? I try PLAIN option in select statement which is described in msdn library as: "PLAIN: Prevents column headings from appearing in the query output that is displayed. You can use PLAIN whether or not a TO clause is present. If an INTO clause is included, PLAIN is ignored. " However, I tried in both SQL 2000 and SQL 7, none of them supports it. Any ideas? Thanks in advance. Joan
View Replies !
Hiding Or Removing Column Output From Select Statement
I'm executing the following... select COL1, min(COL2) from TABLE group by COL1 the table has many duplicate entries, where COL2 is the primary key and unique, but its the duplicate COL1 entries that have to be removed. I was hoping a simple "delete from table where COL1 not in (select COL1, min(COL2) from TABLE group by COL1)" would do the trick, but obviously in returning two columns from the subselect this won't work. Can I hide the COL2 output from the query that will be put in the subselect? this is a one-off thing, so i'm not overly concerned about overhead or elegance. just need to make it so. tia a
View Replies !
Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings, I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered. How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column. ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name, A.index FROM ... ... ORDER BY A.[Index], A.Name ASC END ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index] END Thanks
View Replies !
Select Statement That Will Output Related Rows With Different Column Data Per Row?
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like: rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc... where for each ID, I have 3 rows that are associated with it and with different corresponding values. I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table. i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc.. After my loop, I will just do a select * from #someTempTable Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
View Replies !
Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g. Table 1 Org aaa:aaa:aa aaa:aaa:ab aaa:aab:aa Table 2 delim Seg1Start Seg1Len Seg2Start Seg2Len Seg3Start Seg3Len : 1 3 5 3 9 2 My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work. Any ideas? Can this be done in SSIS? I'd really appreciate any insight that anyone might have. Regards, Bill
View Replies !
Add Column With Fixed Number Of Values (text) To The Select Statement
Hello, I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
View Replies !
Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized? SET NOCOUNT ON Declare @RandomRecordCount as int, @Counter as int Select @RandomRecordCount = 1000 Declare @Type table (Name nvarchar(200) NOT NULL) Declare @Source table (Name nvarchar(200) NOT NULL) Declare @Users table (Name nvarchar(200) NOT NULL) Declare @NumericBase table (Number int not null) Set @Counter = 0 while @Counter < @RandomRecordCount begin Insert into @NumericBase(Number)Values(@Counter) set @Counter = @Counter + 1 end Insert into @Type(Name) Select 'Type: Buick' UNION ALL Select 'Type: Cadillac' UNION ALL Select 'Type: Chevrolet' UNION ALL Select 'Type: GMC' Insert into @Source(Name) Select 'Source: Japan' UNION ALL Select 'Source: China' UNION ALL Select 'Source: Spain' UNION ALL Select 'Source: India' UNION ALL Select 'Source: USA' Insert into @Users(Name) Select 'keith' UNION ALL Select 'kevin' UNION ALL Select 'chris' UNION ALL Select 'chad' UNION ALL Select 'brian' select 1 ProviderId, -- static value '' Identifier, '' ClassificationCode, (select TOP 1 Name from @Source order by newid()) Source, (select TOP 1 Name from @Type order by newid()) Type from @NumericBase SET NOCOUNT OFF
View Replies !
Dynamic SQL And Table Names
Hi all, I have been attempting to construct some dynamic SQL using a table name as a parameter, and call it using sp_executesql, in SQL Server 2005. According to all of the searches and articles I have read today, the following should work: DECLARE @rownumINT DECLARE @tabNamNVARCHAR(100) DECLARE @SQLStringNVARCHAR(500) DECLARE @ParmDefinition NVARCHAR(500) SET @rownum = 35 SET @tabNam = 'AdminScriptHistory' SELECT @SQLString = N'SELECT * FROM @table where executionorder=@row' SET @ParmDefinition = N'@table VARCHAR(100), @row INT' EXEC sp_executesql @SQLString, @ParmDefinition, @tabNam,@rownum However, it fails with: Msg 1087, Level 15, State 2, Line 1 Must declare the table variable "@table". Anybody know why? Cheers
View Replies !
Crosstab Qry With Dynamic Field Names
Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto
View Replies !
Dynamic Table Names In Queries
Is there a way to dynamically build a table name for a query without making the entire query a variable and using EXEC? The query will be against a remote server and I need to construct the full table name from a variable server name and variable database (the table name itself will be constant). Thanks in advance. Philippe
View Replies !
Working With Dynamic File Names
I am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui. I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated. thanks, Scott
View Replies !
Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns
Hi Craig/Kamal, I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative. Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information. We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user. I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns. Your help in this respect is highly appreciated! Thanks, Hi Anthony, I am glad the Web cast was helpful. Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using. I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 Thanks Craig Guyer SQL Server Reporting Services
View Replies !
Table Column Names = Dataset Column Values?!
I need to create the following table in reporting services PRODUCT April March Feb 2008 2007 2008 2007 2008 2007 chair 8 9 7 4 4 4 table 3 4 5 6 4 6 My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers??? Thanks in advance!!!
View Replies !
Dynamic Table Names In Stored Procedure...
Hello all, Im just wondering... is there any way to have dynamic table names, so that, say for instance, i have 4 stored procedures, that all do the same thing, just to four different tables. is there any way to have 1 stored procedure, and pass through the table name??? Adding the four statements into one statement is not an option, as i only need to execute one at a time..., not all four at once... Cheers, Justin
View Replies !
Creating /accessing Tables With Dynamic Names
Hi I have developed an application in ASP/SQL server 7. Ths system is single user. One of the tables is updated by usr actions( say Table A). To make it multi user. I want to create table such as A_Username. How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username . Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option? pls suggest
View Replies !
PIVOT With Dynamic Columns Names Created
I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic. For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) : SELECT Division, [2] AS CurrentPeriod, [1] AS PreviousPeriod FROM ( SELECT Period, Division, Sales_Amount FROM Sales.SalesOrderHeader WHERE ( Period = @period OR Period = @period - 1 ) ) p PIVOT ( SUM (Sales_Amount) FOR Period IN ( [2], [1] ) ) AS pvt Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1). Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900 What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ? Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter. [2] AS CurrentPeriod, [1] AS PreviousPeriod FOR Period IN ( [2], [1] ) I have tried to use the @period but it doesn't work. Thanks in advance. Kenny
View Replies !
Dynamic/variable Field Names In Expressions
I have a report containing 3 datasets. One of these sets is a record set of 2 columns (Name,Priority). I use this dataset as a list so I can create a page for each Name is the list. On each page, I would like to access the other 2 datasets and depending on the Name in the list (Fields!Name.Value), I need to access different Fields. This can easily be done by making a long =IFF list but I would rather make this dynamic, cause the IFF statement becomes a long mess. One of the Names of the list is Joe so if the list name = Joe, his columns need to be read. =Max(Fields!ABSDIFF_JOE.Value, "APPLICATIONS") Or =round(sqrt(pow(StDevP(Fields!DIFF_TJOE.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_TJOE.Value, "APPLICATIONS"),2)),2) When the next list/page is created, the next name needs to be used to get the data from the correct field/column =Max(Fields!ABSDIFF_MAX.Value, "APPLICATIONS") or =round(sqrt(pow(StDevP(Fields!DIFF_TMAX.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_TMAX.Value, "APPLICATIONS"),2)),2) Can I somehow create this as such: =Max(Fields!ABSDIFF_ & Fields!Name.Value &.Value, "APPLICATIONS") or =round(sqrt(pow(StDevP(Fields!DIFF_T& Fields!Name.Value &.Value, "APPLICATIONS"),2) + pow(avg(Fields!DIFF_T& Fields!Name.Value &.Value, "APPLICATIONS"),2)),2) As you can see, I replaced the name (Joe or Max) with "Fields!Name.Value". Unfortunately this doesn't work. Any advice would be appreciated. Thanks Rob Ps. The record set used as input for this report is rather difficult to change.
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
|
TRACKER
