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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
How To Use SP's Output In The SELECT Statement
hi guys!it's very very simple question for you mighty sql DBAs. but very hardfor a developer like me who is very very new to MS SQL.anyways the problem is i want to use one SPs out to in the SELectstatement. here is an example :select * from sp_tables tablename like 'syscolumns'please note that this is just an example. i'm using different SP but iwant to use in the same way.if anybody has anything to say. please write to me. i would be glade toread your repliesThanks,Lucky
View Replies !
Interesting Output In A Select Statement
Hi, If I query like this, am getting the following Output.. Why is the part before '_ ' gets truncated and gets displayed as result ? SELECT 10_to_100 _to_100 ----------- 10 SELECT 25_from _from ----------- 25 If anybody is familiar with the reason,pls share... cheers ! ash
View Replies !
How To Assign The SELECT Statement Output To A Local Variable?
In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
View Replies !
Can You Have Multiple Output Parameters? Difference Between A Select Statement?
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible? Do I need just a select statement to do this? What is the difference between the output and select statements? Thanks in advance.
View Replies !
Using A Select Statement To Only Insert In Certain Rows
Hey, I am not sure how to really explain this, but I'll give it a try. I am looking to use a select statement in a way that I can tell it which rows to insert in depending on when only one result is returned. For example, if I run this statement: SELECT Column1, Column2, Column3 FROM #Temp1 The result set is: Column1---Column2---Column3 99--------6756756---55555 44--------55---------NULL Column3 as only the one returned value, so I do not want it associated with any of the other rows, so I need this: Column1---Column2---Column3 NULL------NULL------55555 99--------6756756---NULL 44--------55---------NULL Another example: The returned result now is: Column1---Column2---Column3---Column4 99---------6756756---55555-----NULL 42---------55---------NULL------12345 So I need: Column1---Column2----Column3----Column4 NULL-------NULL-------55555------NULL NULL-------NULL-------NULL-------12345 99---------6756756----NULL-------NULL 44---------55----------NULL-------NULL Does this make sense, and/or is it even possible? I know it could be more of a presentation thing, but I would like to know how to do it in the code behind. Thanks
View Replies !
Select Statement Combining Rows
I need some help with a query . I have two tables "config" and "item". I have table config as follows: ConfigID ItemID With Values ConfigID ItemID 14583 2776 14583 2798 14583 3112 And table item as follows: ItemID ItemTypeID ItemValue With Values ItemID ItermTypeID ItemValue 2776 1 123 2798 2 ABC 3112 3 789 So the query: SELECT ConfigID, (SELECT ItemValue WHERE ItemTypeID = '1') AS Model, (SELECT ItemValue WHERE ItemTypeID = '3') AS Minor, (SELECT ItemValue WHERE ItemTypeID = '2') AS Customer FROM config c, item i WHERE ConfigID = '14583' AND c.ItemID = i.ItemID Produces the result: ConfigID Model Minor Customer 14583 123 NULL NULL 14583 NULL NULL ABC 14583 NULL 789 NULL How do I change the above query to get one row: ConfigID Model Minor Customer 14583 123 789 ABC Thanks for your help
View Replies !
Select Statement Not Importing All Rows From AS400
Hello all, I am trying to select a specific amount of rows from an AS400 table and when I do so it only shows a specific amount of rows regardless of how many there actually is. If we run it multiple times, it displays the same amount of rows. We went into the iSeries ODBC and changed the timeout setting and the Record Blocking Size (increased it) and it did pull more rows but still not what we were looking for. We suspect it has something to do with pulling the specific amount of blocks and instead of continue to pull, it stops when it reaches the first blocking limit. I can however pull the information through MS Access with the same settings. The query we are using to pull the information is Select * Into Table From Openquery(AS400,'Select * From Tablename') Was wondering if anyone has seen this before and if so if they found a fix so we can pull everything we are looking for. Thanks!
View Replies !
Getting Data (rows) And Output Params From A Query
I have a stored procedure that I use to return Purchase Orders from our PO system. It returns the data rows for PO's that match the criteria passed in (including the page to show etc.) + it returns two output params, Number of rows and Number of Pages. Using query analyzer I can confirm the query works exactly as we want. I cannot however seem to get the data out to our ASP.net app. Here is a function that I use in one of my classes: <code> Function fnListPOsByCoordinatorIDPaged(ByVal strCoordinatorID As String, ByVal intPOStatusID As Int16, _ ByVal intUserTypeID As Int16, ByVal intArchived As Int16, _ByVal intPageNum As Int32, ByVal intPerPage As Int32, _ByVal strConn As String) As SqlClient.SqlDataReader Dim dr As SqlClient.SqlDataReader SqlConnection1.ConnectionString = strConn prListPOByCoordinatorPaged.Parameters("@CoordinatorID").Value = strCoordinatorIDprListPOByCoordinatorPaged.Parameters("@POStatusID").Value = intPOStatusIDprListPOByCoordinatorPaged.Parameters("@UserTypeID").Value = intUserTypeIDprListPOByCoordinatorPaged.Parameters("@Archived").Value = intArchivedprListPOByCoordinatorPaged.Parameters("@PageNum").Value = intPageNumprListPOByCoordinatorPaged.Parameters("@PerPage").Value = intPerPage SqlConnection1.Open()dr = prListPOByCoordinatorPaged.ExecuteReader(CommandBehavior.CloseConnection) Me.Pages = prListPOByCoordinatorPaged.Parameters("@Pages").ValueMe.Rows = prListPOByCoordinatorPaged.Parameters("@Rows").Value If Me.Rows / intPerPage > Me.Pages Then Me.Pages = Me.Pages + 1End If Return dr prListPOByCoordinatorPaged.Dispose()SqlConnection1.Close()SqlConnection1.Dispose() End Function </code> It does not crash, it returns my data reader with the correct records. Unfortunately my property values are returned as 0. They should have values. Anyone know how to do this? Thanks.
View Replies !
Finding Rows With Missing Related Rows
Hi, I need to write a query which I have never attempted before and could do with some help.... I have a Groups table and a Users_Groups look up table. In this model, users can only be assigned to 1 group. If a group is deleted, a trigger should fire and delete any rows in User_Groups having a matching Groups.Ref. Unfortunately, the trigger hasn't been firing and I now have a load of defunct rows in Users_Groups relating users to groups which do not exist.I now need to find all of these defunct rows in Users_Groups so that I can delete them. How can I find rows in Users_Groups where the parent rows and refs in Groups are null? I've tried searching the net for something similar but don't even know how to word the search properly to get any half relevant results. Cheers PS, I do realise I need to tighten the constraints on my database
View Replies !
Copy Rows To The Same Table And Its Related Data In The Other Table
Hi All, I have 2 tables People & PeopleCosts. PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs.. Kindly help. thanks in advance. Myl
View Replies !
SELECT Query (rows Returned In One Column)?
Hi there, I¡¦ve got a table with the following as well as other info: User ID DirectoryTypeID (int) Region ID (int) I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query: subRegionAreaID directoryTypeID --------------- --------------- 3 1 3 2 3 3 3 9 If need these results to be: subRegionAreaID directoryTypeID --------------- --------------- 3 1, 2, 3, 9 Is this possible? Many Thanks!! ƒº Grazi
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 !
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 !
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 !
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 Replies !
Error When I Try To Add Output Column In In Data Conversion
Here is what I get. Is this an install problem or is this how this software works?? =================================== Error at Data Flow Task [Data Conversion [720]]: An output cannot be added to the outputs collection. (Microsoft Visual Studio) =================================== Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.InsertOutput(DTSInsertPlacement eInsertPlacement, Int32 lOutputID) at Microsoft.DataTransformationServices.Design.Controls.ComponentMetaDataTreeView.AddOutput()
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 !
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 !
Delete Rows With Duplicate Column Data But Unique Row Data
Hello, This probably has been addressed before but I was unable to get the search to work properly on this site. I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example : Row 1 Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Row 2 Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc. Row3 Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc. Row 4 Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc. Etc. Etc. I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique. I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks, Kerry
View Replies !
Extract Column Data From Flat File Error Output
Hi Friend, I am stuck with a problem and need your help. As we know, all columns that go to error flow of flat file source connection are displayed as a single column e.g. FlatFileSourceErrorOutputColumn, but my requirement is to extract the first column value from this FlatFileSourceErrorOutputColumn, my data is dilimeted by "|" pipe operator. I have created a script component to deal with this. However if we take FlatFileSourceErrorOutputColumn column as input column in script component, it comes as BLOB data. I wrote below code in transformation script component to extract BLOB data from column in string form and then do a Left function search to take first column out. When I am running this script component I am getting '??????????' question marks as a result in Row.Pname. Can anyone please help me understand if I am doing anything wrong in this script or suggest a better way to take the data out? I appreciate your help. Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' ' Dim Error_Data As String Dim Column_1 As String Dim Len As Integer Dim Buffer As Byte() Buffer = Row.FlatFileSourceErrorOutputColumn.GetBlobData(0, CInt(Row.FlatFileSourceErrorOutputColumn.Length)) Error_Data = System.Text.Encoding.Unicode.GetString(Buffer) Len = Error_Data.IndexOf("|") Column_1 = Left(Error_Data, Len - 1) Row.Pname = Column_1 End Sub Thanks, Kul
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 !
Select Data From Multiple Rows To One Row
Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction. stocknumber transtype transsubtype balance s01 in cust 100 s01 out cust 200 s01 in deal 300 s01 out cust 100 s02 in deal 200 s02 out cust 300 s02 in cust 100 s02 out cust 200 s02 in cust 300 I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like (in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below stocknumber in,deal out,cust in,cust s01 300 300 100 s02 200 500 400 I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .
View Replies !
Trying To Modify Some Column Data In Some Rows
Trying to change some of the rows in a table specifically one column. column type is varchar ex. of data current desired $345,434.0 345434.0 (stripping out of $ & commas) 435.0 leave as is general txt leave as is having trouble updating data in table tried using temp table, but update command make it so it won't see table ex. piece of code update currency_conversion set currency_varchar = cast (cast (currency_varchar as money)as varchar) select * from currency_conversion where substring (currency_varchar from 1 for 1) = '$' ; sql 2000 trying to do from query analyzer thanks glnsk8ter@yahoo.com thanks Glenn
View Replies !
Using Output From A Stored Procedure As An Output Column In The OLE DB Command Transformation
I am working on an OLAP modeled database. I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff). I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails. The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output. What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column? I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it. Thanks in advance for any assistance you can provide.
View Replies !
Excel Destination Data Flow Component Shows No Sheet Name Or Output Column Names For Mappings
I have a data flow that consists of OLE DB source which calls a stored proc that returns a result set data conversion Excel destination I am in design mode in Business Intelligence studio. My excel destination (with an Excel Connection) shows no sheet name though I have an execute SQL task before the data flow to create the excel table called SHEET1. Needless to say, there are no output columns visible to do any mappings. I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect. I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements. I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet. Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms. What am I missing or doing wrong? Thanks in advance
View Replies !
Error Related To IF Statement In Query
I'm having trouble getting a query to run that contains an IF statement: ***************************************** DECLARE @lid int, @ptf varchar(50) SET @lid = 3 set @ptf = '05-00005' SELECT tblInfoData.PTFileNumber AS [PT File Number], IF @lid > 3 BEGIN tblInfoData.OrigGrantor AS [Original Grantor], END tblPropertyData.propStreet AS [Property Address], tblPropertyData.propCity AS City, tblPropertyData.propState AS State, tblPropertyData.propZip AS Zip, tblSaleData.OrigSaleDate AS [Original Sale Date], tblInfoData.Status AS Status FROM tblInfoData INNER JOIN tblPropertyData ON tblInfoData.PTFileNumber = tblPropertyData.PTFileNumber INNER JOIN tblSaleData ON tblInfoData.PTFileNumber = tblSaleData.PTFileNumber WHERE CONTAINS(tblInfoData.PTFileNumber, @ptf) ********************************************* This throws the following error in Query Analyser: Server: Msg 156, Level 15, State 1, Line 12 Incorrect syntax near the keyword 'IF'. Server: Msg 170, Level 15, State 1, Line 14 Line 14: Incorrect syntax near 'tblInfoData'. Any help/ideas? thanx </c>
View Replies !
Help With Combining Data From Multiple Rows Into One Column In A View
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
View Replies !
Matrix That Adds A New Column After 5 Rows Are Filled With Data
Hello, I have a report in wich I show a list of countries. The length of this list differs each month and comes from a SSAS datasource. I want to show this list on my report in a matrix like this: country1 country6 country11 country2 country7 country12 country3 country8 country4 country9 country5 country10 How can I do this? I need some sort of check that counts the number of countries added and then adds a new column after 5 countries. I'm sure there is somebody that did this or knows how to do this. Thanks in advance! Jorg.
View Replies !
|
TRACKER
