Multiple Values For Single Row
hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..
now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...
so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..
so now every user must have a list of other userid's.. as contact list..
Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !
if it helps..iam using sql server express edition..and iam accessing using asp.net/C#
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Multiple Columns With Different Values OR Single Column With Multiple Criteria?
Hi, I have multiple columns in a Single Table and i want to search values in different columns. My table structure is col1 (identity PK) col2 (varchar(max)) col3 (varchar(max)) I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as SELECT TBL.col2,TBL.col3 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col2,'engine') TBL1 ON TBL.col1=TBL1.[key] INNER JOIN CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON TBL.col1=TBL2.[key] Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record. I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result = 1 row But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA SELECT TBL.col4 FROM TBL INNER JOIN CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON TBL.col1=TBL1.[key] Result=0 Row Any idea how i can write second query to get result?
View Replies !
View Related
Multiple Values Into Single Cell
Taking the Northwind database as an example,I have an order table: 'Orders'a order details table: 'Order Details'and a products table: 'Products'For reasons best ignored, I want to produce a query which has columns:Orders.OrderID, Productswhich has results like:10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella diGiovanni'10249, 'Tofu, Manjimup Dried Apples'________so for those who don't really know what I'm on about and haven't gotaccess to northwind, I want the second cell to contain the returnedcolumn of a second query (but in text format)could anyone suggest a way this could be done? am I entering the landof cursors?Many thanks,Neil
View Replies !
View Related
Store Multiple Values In A Single Value
was hoping someone couild provide some insight into a problem I'm trying to solve. I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example: CRITERIAID CRITERIA CRITERIAVALUE 1 AGE 2 2 SEX 4 3 GRADE 8 4 LOCALE 16 5 REGION 32 A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values. Has anyone done anything like this before? If so, any help would be appreciated! Thanks in advance!
View Replies !
View Related
How Can I Combine Values Of Multiple Columns Into A Single Column?
Suppose that I have a table with following values Table1 Col1 Col2 Col3 ----------------------------------------------------------- P3456 C935876 T675 P5555 C678909 T8888 And the outcome that I want is: CombinedValues(ColumnName) ---------------------------------------------- P3456 - C935876 - T675 P5555 - C678909 - T8888 where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-' So is there any way to achieve this?
View Replies !
View Related
Show Multiple Values In Single Textbox Comma Separated
I have a field called "Owners", and it's a child to an "Activities" table. An Activity can have on or more owners, and what I'd like to do is some how comma separate the values that come back if there are more than one owners. I've tried a subreport, but because the row is colored and if another field, title, expands to a second row (b/c of the length) and the subreport has just one name, then the sub-report has some different color underneath due to it being smaller in height. I'm kinda stuck on how to do this. Thanks!
View Replies !
View Related
The Best Method Of Storing Multiple Values For A Single User Criteria In The Database ?
Let's say you had a User table and one of the fields was called Deceased. It's a simple closed-ended question, so a bit value could be used to satisfy the field, if the person is dead or alive. Let's say another field is called EyeColor. A person can have only one eye color and thus one answer should be stored in this value, so this is easy as well. Now, let's say I want to store all the languages that a specific user can speak. This isn't as easy as the previous examples since it's not a yes or no or a single-value answer. I haven't had much experience with working with databases so I've come up with two possible ways with my crude knowledge hehe. In terms of inputting the multi-answer values, I suppose I could use a multiple-selection listbox, cascading dropdowns, etc. Now, here are the 2 solutions that came to mind..... 1) Make a field called LanguagesSpoken in the User table. When I process the selections the user makes on the languages he knows, I can then insert into the LanguagesSpoken field a string "English, Spanish, Czech" or IDs corresponding to the languages like "1, 5, 12" (these IDs would be referenced from a separate table I guess). I would use commas so that later on, when I need to display a user's profile and show the user's languages, I can retrieve that long string from the LanguagesSpoken field, and parse the languages with the commas I've used. Using commas would just be a convention I use so I would know how to parse (I could have used "." or "|" or anything else I guess) the data. 2) Forget about the LanguagesSpoken field in the User table altogether, and just make a LanguagesSpoken table. A simple implementation would have 3 fields (primary key, userId, languageId). A row would associate a user with a language. So I would issue a query like "SELECT * FROM LanguagesSpoken WHERE userId=5" (where userId=5 is some user). Using this method would free me from having to store a string with delimited values into the User table and then to parse data when I need them. However, I'm not sure how efficient this method would be if the LanguagesSpoken table grows really large since the userIds would NOT be contiguous, the search might take a long time. I guess I would index the userId field in the LanguagesSpoken table for quicker access? OR, I may be going about this the wrong way and I'm way out on left field with these 2 solutions. Is there a better way other than those 2 methods? I haven't work extensively with databases and I'm just familiar with the basics. I'm just trying to find out the best-practice implementation for this type of situation. I'm sure in the real world, situations like this is very common and I wonder how the professionals code this. Thanks in advance.
View Replies !
View Related
Passing Multiple Values To Single Report Parameter Using Cube Surce
Hello Freinds, I'm facing a small problem while passing Multiple Values to a Single Report Parameter, this report is using a Cube as its source. I'm able to assign and send a single value to the report parameter but i'm not finding a way to send more than one value.. The following is the code which i am using to populate my Parameter Dim Param(0) As Microsoft.Reporting.WebForms.ReportParameter Param(0) = New Microsoft.Reporting.WebForms.ReportParameter("BUDASADepartment", "[BUDASA].[Department].&[Accommodation]") In the same 'BUDASADepartment' i want to add one more value as "[BUDASA].[Department].&[Others]" but i'm not able to figure out a way of do'ing it. Any Suggestions , Thanz !
View Replies !
View Related
Multiple Databases And Multiple Exe For A Single Solution
Dear Reader,Currently Am working on a Management Information System.Need to develop some part of the solution as almost Hard Coded Details: Both Front end and Database carry default valuse...which will never change in the near future. And some parts are depending on Changing rules ....so to be developed as separate exes...and separate databse are requires so ...Changes if needed can be adopted easily...Please guide ...How to manage abobe requirement?Please feel free to write for further clarifications.SuryaPrakash Paaatel--Message posted via http://www.sqlmonster.com
View Replies !
View Related
Retrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports
I am trying to create a report using Reporting Services. My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report. I can currently get one value but how to get the information I need to be able to use in my reports. So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped. Any help would be appreciated. Thanks. I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work. What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields. As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem Thanks for the suggestions that were made, I apoligize for not making the problem clearer. Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting. --Pulls the Service Opportunity SELECT cs.value AS "Service Opportunity" FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid WHERE ca.name = 'Service Opportunity' --Pulls the Number of Hours SELECT cs.value AS 'Number of Hours' FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid WHERE ca.name ='Num of Hours' --Pulls the Person Grade Level SELECT cs.value AS 'Grade' FROM Cstudent cs INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid WHERE ca.name ='Grade' --Pulls the Person Number, First and Last Name and Grade Level SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade" FROM student s INNER JOIN cperson cs ON cs.personid = s.personid INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid WHERE cs.value =(SELECT cs.value AS 'Grade' WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
View Replies !
View Related
'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go
Please be easy on me...I haven't touched SQL for a year. Why given; Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF do I get Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ','. for the following; Code Snippet INSERT INTO Patients (ID, FirstName,LastName,DOB) VALUES ( '1234-12', 'Joe','Smith','3/1/1960'), ( '5432-30','Bob','Jones','3/1/1960'); Thank you, hazz
View Replies !
View Related
SqlDataReader - Pulling Multiple Values Into Multiple Variables
Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{ String UserName; String userId; String HiveConnectionString; String Current_Location; ArrayList Location; public String Location1; public String Location2; public String Location3; //Int32 x = 0; private void Page_Load(object sender, EventArgs e) { if (User.Identity.IsAuthenticated) { UserName = Membership.GetUser().ToString(); userId = Membership.GetUser().ProviderUserKey.ToString(); HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647"; using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString)) { // Map Updates MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT Location FROM tracker WHERE Location = IsOnline = '1'"); Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Locationcmd.Connection = conn; conn.Open(); MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader(); while (LocationReader.Read()) { Location1 = LocationReader.GetString(0); //Location2 = LocationReader.GetString(1); // This does not work.. } LocationReader.Close(); conn.Close(); // IP Display MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand( "SELECT UserName FROM tracker WHERE PKID = ?PKID"); Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Checkcmd.Connection = conn; conn.Open(); object UserExists = Checkcmd.ExecuteScalar(); conn.Close(); if(UserExists == null) { MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand( "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)"); Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Insertcmd.Connection = conn; conn.Open(); Insertcmd.ExecuteNonQuery(); conn.Close(); } else { MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand( "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID"); Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress; Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName; Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId; Updatecmd.Connection = conn; conn.Open(); Updatecmd.ExecuteNonQuery(); conn.Close(); } } } } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone!
View Replies !
View Related
Select Several Values Into A Single Variable
Can somebody please tell me whether the following syntax is supportedor whether it's a "feature" that will someday stop working. It works inboth SQL Server 2000 and 2005 at the moment.declare @var varchar(1000)set @var = ''select @var = @var + colx from some_table where col1 = some_valuecolx is a varchar or at least is cast to one as part of the selectstatement. If the where clause would normally return more than one row,all returned values for colx are concatenated into @var.I've not seen this syntax before but that doesn't make it wrong ;-)Malc.
View Replies !
View Related
Rowset Values In A Single String
I am not close to an sql server today and this question was posed to me. can someone hook me up? " I want to query a column of values and place them into a single string seperated by commas" (as a function) Table a 123 456 789 321 654 987 output 123,456,789,321,654,987 thanks in advance
View Replies !
View Related
Splitting Up Of Values In Single Column
Hi, I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...? Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table) NameZipCode Area119930,19970,19971,19944 Area219934,19938,19901,19903,19904 Area319994,19838 output Table should be: NameZipCode Area119930 Area119970 Area119971 Area119944 Area219934 Area219938 Area219901 Area219903 Area219904 Area319994 Area319838 Any thoughts on this would be of much help !!.. Thanks
View Replies !
View Related
SQLCE V3.5: Single SDF With Multiple Tables Or Multiple SDFs With Fewer Tables
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are: 1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup. 1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types. 1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data. When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately. I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that. Thanks in advance for any help/suggestions, Bob
View Replies !
View Related
Concatenate Values From Same Column But Different Record In Single Row
Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem: I have a sql query which brings me many records. This records have a column in common which have the same value (COL1) There is a second column (COL2) which has different values bewteen these records. I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result. Let me give you an example: COL1 COL2 RECORD1 1-A HHH RECORD2 1-A GGG RECORD3 1-B LLL RECORD4 1-B MMM RECORD4 1-B OOO RECORD5 1-C NNN Me result should be: COL1 COL2 RECORD 1-A HHHGGG RECORD 1-B LLLMMMOOO RECORD 1-C NNN It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS. Thanks for any help you can give me.
View Replies !
View Related
Single Or Multiple Sp ???
greetings,i was wondering is it better to have multiple small stored procedures or one large store procedure ???? example : 100 parameters that needs to be inserted or delete ..into/from a table.............is it better to break it up into multiple small store proc or have 1 large store proc....thanks...............
View Replies !
View Related
Bcp Single CPU Vs Multiple CPU
I'm doing a BCP of a large table 37 million rows. On a single CPU server, SQL 7, sp 3, with 512 meg of RAM, this job runs in about 3 hours. On a 8 way server with 4 Gig of RAM, SQL 7 Enterprise, this job runs 12 hours and is only a third done. The single CPU machine is running one RAID 5 set while the 8 way server is running 4 RAID 5 sets with the database spread out over two of them. Is there something obvious that a single CPU box would run this much faster?
View Replies !
View Related
Select Comma Separated Values From Single Column
Hi, I have a table -- Table1. It has two columns -- Name and Alpha. Alpha has comma separated values like -- (A,B,C,D,E,F), (E,F), (D,E,F), (F), (A,B,C). I need to pick the values of column -- Name , where in values of Alpha is less than or equal to 'D'. I tried <=, but got only values less than 'D', but was not able to get equal to 'D'. Any suggestions??
View Replies !
View Related
How To Group Multiple Row As Single Row
eg say. i have a table emp data ---- id name phone no 1 smith 423-422-5226 1 smith 414-255-5252 2 george 511-522-2525 2 george 524-522-2428 ........ ........ i need output as 1 smith 423-422-5226, 414-255-5252 2 george 511-522-2525, 524-522-2428 ..... can u any one help me
View Replies !
View Related
Multiple Copy Of A Single Row
Hi All, Dont mistake me for asking such a small question, im little bit confused. Result of my query gives 1 rows. i want some hundred copy of that single row, how can i do that. Right now i put a while loop and i get the things done, but i know its a wrong way. plz guide me With Regards Amjath PS:if anybody having good tutorial for sql pls pass it to me
View Replies !
View Related
Single Or Multiple DB For Different Systems?
Hi everyone, Im having a hard time deciding what approach should I take. The scenario is this: I have developed various systems (inventory, HR, accounting, etc.). All this systems are (and should be) tightly integrated with one another. At present, for all these systems, i've used a single DB prefixing the tables with the systems name (eg. Inventory.Items). My question is: did I did the right (and practical) thing? Or should I create a DB for each system to organize them? The problem with multiple DBs is some system uses the other system's table(s). Example, if i created a separate DB for accounting, and a separated DB for inventory, and another for HR, how am I going to relate inventory and HR's accounts to the accounting DB's table? I want a single instance for each table; I don't want to create another account table for inventory or HR so I can enforce integrity. And if different DBs, is there a performance impact on this? Or is there another way? My concern is performance and manageability. Please help. Thanks!
View Replies !
View Related
Single Row Into Multiple Rows
Hi All, We've a table as in the following format: PK_Column1 PK_Column2 Issue_Date1 Issue_Amount1 Issue_Category1 Issue_Reject1 Issue_Date2 Issue_Amount2 Issue_Category2 Issue_Reject2 We need to divide it into two new tables as follows: UniqueID PK_Column1 PK_Column2 And UniqueID PK_Column1 PK_Column2 Sequence_ID Issue_Date Issue_Amount Issue_Category Issue_Reject Unique1 1 Issue_Date1 Issue_Amount1 Issue_Category1 Issue_Reject1 Unique2 2 Issue_Date2 Issue_Amount2 Issue_Category2 Unique3 1 xx xx Unique4 2 xx xx Unique5 3 xx 4 xx There will be one UniqueID for each row. We'll get the uniqueID and PK1 and PK2 in a file. Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table. Can we do this without using cursors? This is going to be one time process. Any ideas are appreciated. Thanks, Siva.
View Replies !
View Related
Single Or Multiple Services
I have 2 systems that will send data to each other. Each system will originate a particular set of messages, there will be no overlap. This scenario has transaction data going to a reporting system and management operations going back to the transactional system. It is semi-related data. I have two patterns in mind, a combined stream of all messages or 2 streams of segregated messages. A. A single service on each instance would originate a set of messages, process the responses, and receive the other instance's messages. The responses and original message from the other system would mix on the same Q. The activation procs would have to handle all message types. The same infrastructure (message types, contracts, Qs, activation stored procs) would be created on both systems. Although, distinct service names and ports would be used on each instance. B. Two services and two Qs. 1 service would originate a set of messages, process the responses. The other service would process messages from the other system. The responses and original message from the other system would be on the separate Qs. There would be 2 infrastructures created. There could be separate activation stored procs. There is just one message type and message validation is only WELL_FORMED_XML. Which is pattern is better for management and performance? Should I create 1 service or 2 on each instance? Either way should work about as well as the other? 2 services are twice as complex to set up. Separation is not necessary, but I like the idea. 1 service will send many more messages (>10x) than the other. Any thoughts?
View Replies !
View Related
Multiple SET/WHERE Within Single UPDATE?
Can an Update statement support multiple SET/WHERE clauses? The following example won't work, but this is what I'd like to be able to do: Code Snippet UPDATE [CPSAgacar].tmp_CTA2 SET [Admit Date] = NULL WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL SET CalcDischDate = Discharge WHERE [CPSAgacar].tmp_CTA2.CalcAdmitDate IS NOT NULL AND CPSAgacar].tmp_CTA2.CalcDischDate IS NULL Thanks.
View Replies !
View Related
Getting Multiple Rows In A Single Row
Hi, I've a temp variable where I'm moving some columns like below: id value type1 type2 0 ab type1val1 type2val1 0 cd type1val1 type2val1 0 ef type1val1 type2val1 1 ab type1val2 type2val2 1 cd type1val2 type2val2 1 ef type1val2 type2val2 What I want to do is group these by their id and get the following o/p ab,cd,ef type1val1 type2val1 ab,cd,ef type1val2 type2val2 The grouped values need to be separated by commas. What I'm doing currently: I'm using a temp variable to put all these values but am unable to coalesce and get the desired o/p. Can anybody help me out? Thanks, Subha
View Replies !
View Related
Is There Any Solution For Dispalying String And Percentage Values In Single Column
i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement. so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and not attended statement for perticular query in same column. is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply same result compare with preview at the time of export to excel sheet plese send solutions ASAP Thanks James vs
View Replies !
View Related
Group By And Count(*) A Single Column Returing Two Counted Values
I am trying to count a column field in a single table and return two count values as one record set using group by. field1 = group by (department) nvarachar field2 = count (closed) datetime I have tried using derived tables with no luck getting the desired result. field2 is a datetime field as indicated I want a count for two conditions 1. WHERE field2 is null 2. WHERE field2 is not null End Results would like this ====== Department | OpenItems | ClosedItems Department1 | 32 | 24 Departmnet2 | 87 | 46 Department3 | 42 | 76 ======= I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments. I realize this is probably a simple answer and I am making this a lot harder than it actually is.... Any suggestions?
View Replies !
View Related
Storing Comma Separated Values In A Single Column Of A Table
Hi, I have a table called geofence. It has a primary key geofence_id. Each geofence consists of a set of latitudes and latitudes. So I defined two columns latitude and longitude and their type is varchar. I want to store all latitude/longitude values as a comma separated values in latitude/longitude columns So in general how do people implement these types of requirements in relational databases? --Subba
View Replies !
View Related
Compressing Multiple Rows With Null Values To One Row With Out Null Values After A Pivot Transform
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string? -- Ryan
View Replies !
View Related
Multiple Columns Into Single Row -- Very Urgent
Hi. I want to return multiple rows into a single row in different columns. For example my query returns something like thisThe query looks like thisSelect ID, TYPE, VALUE From myTable Where filtercondition = 1ID TYPE VALUE1 type1 121 type2 152 type1 16 2 type2 19Each ID will have the same number of types and each type for each ID might have a different value. So if there are only two types then each ID will have two types. Now I want to write the query in such a way that it returnsID TYPE1 TYPE2 VALUE1 VALUE21 type1 type2 12 152 type1 type2 16 19Type1, Type2, Value1, and Value2 are all dynamic. Can someone help me please. Thank you.
View Replies !
View Related
Combine Multiple Records Into Single Row
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
View Replies !
View Related
Spliting Single Row Into Multiple Rows
I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows. My table structure is: create table #scoresheet (Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255), Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255), Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255)); I would like to have three rows with four columns. What I've done so far is create a stored procedure that uses a table variable: create procedure sp_splitsinglerow as declare @Scoresheet_rows_table_var table ( ReviewArea varchar(25), NumericalValue int, VI varchar(10), Comments nvarchar(255)); insert into @Scoresheet_rows_table_var (ReviewArea, NumericalValue, VI, Comments) select Decisions, DNumericalValue, DVI, DComments from #scoresheet The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time. Any ideas on how to achieve that? BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)
View Replies !
View Related
Dump Multiple DBs To Single Device
I'm having trouble doing backups of several databases (on a single server) to one device (a disk file). I created a script with each DUMP statement and when I run it from the query window, it works just fine. But when I create a stored procedure out of the same script, I get errors because the second DUMP statement is trying to access the device that is already being written to by the first DUMP statement. If I split them apart into different stored procedures, then they seem to overwrite each other and I end up with only the last database backed up. I'm trying to put this into a task and that is why I need to put it into stored procedures. Is there a synchronous/asynchronous setting or parameter that I should be using? For now, I'm just dumping each to separate devices, but this is a little sumbersome, since I have four databases to backup for each day of the week. Which gives me a total of 28 separate devices. I'm sure there is a better way of doing this. Does anyone have any suggestions. Thank you in advance.
View Replies !
View Related
SQL Single Query For Multiple Table
Hi friends, I have three table named as Eventsmgmt,blogmgmt,forummgmt.. Each table contain the common column named as CreatedDateTime.. I want to get the most recent CreationDateTime from these three table in single query.. Plzz help me its urgent Thanks
View Replies !
View Related
Multiple Rows Into A Single Field
Hi I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this.. CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner ( @Crime_No varchar(20), @Unit_ID int ) as begin DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int DECLARE @Defect_ID varchar(100) select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID SELECT TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1, dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id, dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone, dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age, dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type, TBL_TR_ACCIDENT_VEHICLE.Registration_Number, TBL_TR_ACCIDENT_VEHICLE.Crime_No, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre, TBL_TR_ACCIDENT_VEHICLE.vehicle_Make, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model, TBL_TR_ACCIDENT_VEHICLE.Unit_ID, TBL_TR_ACCIDENT_VEHICLE.RowID, TBL_TR_ACCIDENT_VEHICLE.UserID, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color, TBL_TR_ACCIDENT_VEHICLE.HP, TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity, TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle, TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight, TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight, TBL_TR_ACCIDENT_VEHICLE.Skid_Length, (select TBL_TR_Person_OutsideDetails.OutSide_state from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state], (select TBL_TR_Person_OutsideDetails.OutSide_City from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City] ---here I faced the problem- /*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/ (SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5)) FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number) select @Defect_ID FROM tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE where tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID AND TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No AND TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner end GO
View Replies !
View Related
How To Merge Multiple Rows Into Single Row
Hi, I have two tables of news feed NewsHeader & NewsDetails NewsHeader: Time Header 10:15:34 AM News1 10:15:34 AM News1 10:15:34 AM News1 11:19:39 AM News2 11:19:39 AM News2 12:35:04 PM News3 12:35:04 PM News3 NewsDetails Time Text RowC 10:15:34 AM ABC 1 10:15:34 AM DEFG 2 10:15:34 AM HIJKL 3 11:19:39 AM AABB 1 11:19:39 AM CCDD 2 12:35:04 PM ZZYY 1 12:35:04 PM XXWW 2 Required Output Time Header Text 10:15:34 AM News1 ABCDEFGHIJKL 11:19:39 AM News2 AABBCCDD 12:35:04 PM News3 ZZYYXXWW Thank you.
View Replies !
View Related
Multiple Columns From Single Cell
Running SQL2000 I have a one to many relationship between Flag and StudentFlag. I'm wanting to select each Flag.FlagID that exist as a column that contains the StudentFlag.Value per StudentFlag.StudentID (or null for that column if that StudentFlag row doesn't exist). This single column appearing multple times in a single dataset has seemed difficult in a select statement. Is it posible in T-SQL? Tables, Keys, and Relationships CREATE TABLE [dbo].[Flag] ( [FlagID] [int] NOT NULL , [FlagName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[StudentFlag] ( [StudentFlagID] [int] NOT NULL , [FlagID] [int] NULL , [StudentID] [int] NOT NULL , [FlagValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Flag] WITH NOCHECK ADD CONSTRAINT [PK_Flag] PRIMARY KEY CLUSTERED ( [FlagID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StudentFlag] WITH NOCHECK ADD CONSTRAINT [PK_StudentFlag] PRIMARY KEY CLUSTERED ( [StudentFlagID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[StudentFlag] ADD CONSTRAINT [FK_StudentFlag_Flag] FOREIGN KEY ( [FlagID] ) REFERENCES [dbo].[Flag] ( [FlagID] ) GO Sample Output StudentID, FlagType1, FlagType2, FlagType3 1, 'yes', '2', null 2, null, null, 'X' 3, null, '7', 'X'
View Replies !
View Related
|
TRACKER
