as you can see, if I delete a record for SectionId 8001, it should delete that record as well as delete the records that has their parent as 8001. Also, the children of these should also be deleted. eg.
If I delete secid 8007, then it should delete 8007, 8009, 8010, 8011
If I delete 8000, it should deleted all the rows found above.
There is no limit on how many levels it can go upto.
Any help is greatly appreciated.
I 'm having trouble with the delete function on Gridview - the update works great but I keep getting the error below when I try to delete DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Order_Details_Products'. The conflict occurred in database 'Northwind', table 'Order Details', column 'ProductID'.The statement has been terminated. I using the products table in Northwind to learn this stuff.
I'm sure there is a way of cracking this, but I can't think of a good solution. Right now I am not happy with the solutions I come up with, one of which takes 4 minutes to run on SQL Server The scenario: User is presented with search page where one or more search terms can be entered/selected. There are no required parameters. It can be any or all of the possibilities presented. Below is a model of the search parameters presented. The user will either select to show more options under Profile ABC, or go down to Profile STU or Profile XYZ to show more options, or even select all Profiles and then select from Type 1 and either a. or. b. or. c. or ALL of the above. I cannot predict what a user will make part of the search query so I have to have a stored procedure ready which can handle any or all of the parameters a user may select. Am I biting off more than I can chew (it seems so)? Or is there an elegant way of handling the unknown combination of search parameters that a user might throw into my sql query? I'm running this under ASP 1.0 and SQL Server 2000.
[check to show the options below] Profile ABC [check to shore more options] Type 1
A. Contains fields for entering another data string and selecting from drop-down boxes B. ditto C. ditto D. ditto [check to show more options] Type 2
A. Contains fields for entering another data string and selecting from drop-down boxes B. ditto C. ditto D. ditto [check to show more options] Type 3
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto C. ditto D. ditto [check to select more options] Type 4
A. Contains fields for entering another data string and selecting from drop-down boxes B. ditto C. ditto D. ditto [check to show more options] Profile XYZ (as above) [check to select more options] Profile STU (as above)
HI. I have 3 tables 1- std with : stdID , programID. 2- Programs with :ProgramID , Cost 3 - Movements with : stdID , balance. the first table contain the stdID and ProgramID , some times the std hasn't programID that mean he hasn't programID. then we return null. if the std has programID there is to cases. the first one he have a movement on his balance then we get the biggest balance for the std. the second case he hasn't any moventen then we get his balance from Programs table by the ProgramID .
I need sql server function that return table like this stdID , Balance that means every std with his Balance. Regards.
This is too complex anyone know how to make it less complex. I am trying to get all the selected fields from contacts into a datagrid where the other fields contain a string in textbox1. This works SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%')) When i add all the rest of the fields it says its too complex. Please Help
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%') AND ([B] LIKE '%' + ? + '%') AND ([BB] LIKE '%' + ? + '%') AND ([BD] LIKE '%' + ? + '%') AND ([BA] LIKE '%' + ? + '%') AND ([BH] LIKE '%' + ? + '%') AND ([BL] LIKE '%' + ? + '%') AND ([BN] LIKE '%' + ? + '%') AND ([BR] LIKE '%' + ? + '%') AND ([BS] LIKE '%' + ? + '%') AND ([BT] LIKE '%' + ? + '%') AND ([CA] LIKE '%' + ? + '%') AND ([CB] LIKE '%' + ? + '%') AND ([CF] LIKE '%' + ? + '%') AND ([CH] LIKE '%' + ? + '%') AND ([CM] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([CR] LIKE '%' + ? + '%') AND ([CT] LIKE '%' + ? + '%') AND ([CV] LIKE '%' + ? + '%') AND ([CW] LIKE '%' + ? + '%') AND ([DA] LIKE '%' + ? + '%') AND ([DD] LIKE '%' + ? + '%') AND ([DE] LIKE '%' + ? + '%') AND ([DG] LIKE '%' + ? + '%') AND ([DH] LIKE '%' + ? + '%') AND ([DL] LIKE '%' + ? + '%') AND ([DN] LIKE '%' + ? + '%') AND ([DT] LIKE '%' + ? + '%') AND ([DY] LIKE '%' + ? + '%') AND ([E] LIKE '%' + ? + '%') AND ([EC] LIKE '%' + ? + '%') AND ([EH] LIKE '%' + ? + '%') AND ([EN] LIKE '%' + ? + '%') AND ([EX] LIKE '%' + ? + '%') AND ([FK] LIKE '%' + ? + '%') AND ([FY] LIKE '%' + ? + '%') AND ([G] LIKE '%' + ? + '%') AND ([GL] LIKE '%' + ? + '%') AND ([GU] LIKE '%' + ? + '%') AND ([GY] LIKE '%' + ? + '%') AND ([HA] LIKE '%' + ? + '%') AND ([HD] LIKE '%' + ? + '%') AND ([HG] LIKE '%' + ? + '%') AND ([HP] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([HU] LIKE '%' + ? + '%') AND ([HX] LIKE '%' + ? + '%') AND ([IM] LIKE '%' + ? + '%') AND ([IP] LIKE '%' + ? + '%') AND ([IV] LIKE '%' + ? + '%') AND ([JE] LIKE '%' + ? + '%') AND ([KA] LIKE '%' + ? + '%') AND ([KT] LIKE '%' + ? + '%') AND ([KW] LIKE '%' + ? + '%') AND ([KY] LIKE '%' + ? + '%') AND ([L] LIKE '%' + ? + '%') AND ([LA] LIKE '%' + ? + '%') AND ([LD] LIKE '%' + ? + '%') AND ([LE] LIKE '%' + ? + '%') AND ([LL] LIKE '%' + ? + '%') AND ([LN] LIKE '%' + ? + '%') AND ([LS] LIKE '%' + ? + '%') AND ([LU] LIKE '%' + ? + '%') AND ([M] LIKE '%' + ? + '%') AND ([ME] LIKE '%' + ? + '%') AND ([MK] LIKE '%' + ? + '%') AND ([ML] LIKE '%' + ? + '%') AND ([N] LIKE '%' + ? + '%') AND ([NE] LIKE '%' + ? + '%') AND ([NG] LIKE '%' + ? + '%') AND ([NN] LIKE '%' + ? + '%') AND ([NP] LIKE '%' + ? + '%') AND ([NR] LIKE '%' + ? + '%') AND ([NW] LIKE '%' + ? + '%') AND ([OL] LIKE '%' + ? + '%') AND ([OX] LIKE '%' + ? + '%') AND ([PA] LIKE '%' + ? + '%') AND ([PE] LIKE '%' + ? + '%') AND ([PH] LIKE '%' + ? + '%') AND ([PL] LIKE '%' + ? + '%') AND ([PO] LIKE '%' + ? + '%') AND ([PR] LIKE '%' + ? + '%') AND ([RG] LIKE '%' + ? + '%') AND ([RH] LIKE '%' + ? + '%') AND ([RM] LIKE '%' + ? + '%') AND ([S] LIKE '%' + ? + '%') AND ([SA] LIKE '%' + ? + '%') AND ([SE] LIKE '%' + ? + '%') AND ([SG] LIKE '%' + ? + '%') AND ([SK] LIKE '%' + ? + '%') AND ([SL] LIKE '%' + ? + '%') AND ([SM] LIKE '%' + ? + '%') AND ([SN] LIKE '%' + ? + '%') AND ([SO] LIKE '%' + ? + '%') AND ([SP] LIKE '%' + ? + '%') AND ([SR] LIKE '%' + ? + '%') AND ([SS] LIKE '%' + ? + '%') AND ([ST] LIKE '%' + ? + '%') AND ([SW] LIKE '%' + ? + '%') AND ([SY] LIKE '%' + ? + '%') AND ([TA] LIKE '%' + ? + '%') AND ([TF] LIKE '%' + ? + '%') AND ([TN] LIKE '%' + ? + '%') AND ([TQ] LIKE '%' + ? + '%') AND ([TR] LIKE '%' + ? + '%') AND ([TS] LIKE '%' + ? + '%') AND ([TW] LIKE '%' + ? + '%') AND ([UB] LIKE '%' + ? + '%') AND ([W] LIKE '%' + ? + '%') AND ([WA] LIKE '%' + ? + '%') AND ([WC] LIKE '%' + ? + '%') AND ([WD] LIKE '%' + ? + '%') AND ([WN] LIKE '%' + ? + '%') AND ([WR] LIKE '%' + ? + '%') AND ([WS] LIKE '%' + ? + '%') AND ([WV] LIKE '%' + ? + '%') AND ([YO] LIKE '%' + ? + '%'))
hi how are you please help me in my problem which i can't make it. Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category. now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table. is that possible ? please if any one help can me in my problem.
I have 2 tables, say table1, and table2. There is a DocID (primary key) in table1. In table2, DocID is the foriegn key. There can be more than 1 DocID.
how do I join these two tables, such that I get all the otherID's for each DocID. ie., DocID OtherID 1 2 and 10 and 13 and 25 2 3
i am writing this query to display search results on a search page (with keyword search) and so, if I display the result in more than one row, then the user might think that there is more than document...whereas the case is that there is only one document with more than one other ID's.
is there any way I can do this? display...more than 1otherID in the same row for the same DociD? Currently, I am using a left outer join of table1 and table2.
I cant get "order by" to work in this sql query..I use this query: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"and I want to add this some where to get 12 random records: "ORDER BY NewID()"I tried this: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()"" but get the error:"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"I can´t figure out how I should write the query..Somebody have any ideas??/Radiwoi
ID GroupID User 1 101 Tom 2 101 Mark 3 101 Clark 4 102 Tom 5 102 Mark 6 103 Tom 7 103 Clark 8 104 Tom 9 104 Clark 10 105 Tom 11 105 Bred
the users of Group 101 are Tom,Mark,Clark the users of Group 102 are Tom,Mark the users of Group 103 are Tom,Clark the users of Group 104 are Tom,Clark the users of Group 105 are Tom,Bred
I want to show Tom that
Both You and Clark are together in 3 groups Both You and Mark are together in 2 groups Both You and Bred are together in 1 group
I might have to redesign the tables for this, but I'll ask anyway. I have a table with the following fields:
Email - VarChar Seminar - Int PeckingOrder - Int.
As I add addresses to the table, each one has a Seminar, and then each Seminar has a Pecking Order Value. If an email address shows up for more than one seminar, it can have multiple records. IE:
for address, seminar, and pecking order would be sample entries into the table. Give or Take 1000 records in the table at any given time. What I want to pull out is:
Distinct Email Addresses For each Email Address - The Max(PeckingOrder) And the Seminar that's associated with Max(PeckingOrder)
For the sample data set above, I'd want to see these two records returned by the query:
email1 - 1523 - 424 email2 - 1524 - 235
I can't seem to get the Having / Where clause right to pull those two records properly. Anyone have any suggestions?
Thanks, --Daniel
Edited - Didn't realize the BB removed email addresses.
SELECT goto_last_name as 'Manager', advisor_name, advisor_ao_number as 'AO', COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors', SUM(Weekly_TOS_GDC) as 'TOS GDC' FROM weekly_condensed_tb WHERE advisor_platform_number = 1 AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28 AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) GROUP BY goto_last_name,advisor_ao_number,advisor_name HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
In the query above i select a range from the date - 28 days and do a sum on the TOS GDC column. What i am trying to do is have another query where the range will be - 56 and maybe - 86 so i get the SUMS for that as well. I need to display this in SQL Reporting Services and i can only have one DataSet returned otherwise i will not be able to bind it to one table.
How can i go about this so i return one set of data for 3 different date ranges.
I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks. Background: 1. The innermost query (using the dynamic statements) will run fine on its own. 2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors. 3. Also, the entire query runs fine as a view (with hard coded vals for column and value). 4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.
I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ..........., Sum of Amount for December, Total for All months, Average for all months.
I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?
Hi, I want to create a query with which I must compare the production demands with the production results. The production demands can be get by the join of two tables. The production results can be get from an aggregate of 4 tables. The connection of these two objects rely on two fields that exist in both two objects. In order to show all the production demands I must left join the two fields from the demands object to the two fields exist in the aggegate production object. In MsAccess the only way to do it is to create 2 queries one for the demands and one aggregate for the production and in a third query create two left joins from the demands query to the production query and get the right results. How can I do it with MSSQL Server with a query??
BLOG --POSTER_ID --POSTED_BY (a FK refer to USERID) --UPDATED_BY (a FK refer to USERID)
USER --USER_ID --NAME
I want to retrieve the info for all posts that posted and updated by different users, also the query returns both user names for every posted and updated action.
I can write two queries to do that
select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.POSTED_BY = U.USER_ID
select B.POSTER_ID, U.NAME from Blog B inner join USER U ON B.UPDATED_BY = U.USER_ID
Then write some code to combine the result for the posted and updated user name. But this is not efficient.
I am wondering how can I write one query to archeive that?
Hi Guys Hope you can help with this - it's certainly got me scratching my head. I'm Querying a Call Centre Database I have a Table of Call Data with a Start Time & End Time of the Call. (CallID,Started,Ended)10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.0001003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.00010942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.00010942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.00010942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.00010942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.00010942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.00010942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000I'm trying to produce Stats that tell me how many Calls were live in any one given minute. Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute. I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me. WHILE(@cnt <= 43200)BEGINSELECT @MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutesINSERTINTO AprilMinutes VALUES(@MaxDate,NULL)SET @cnt = @Cnt +1 ENDWhich produces a nifty little table with 01/04/2007 00:09:0001/04/2007 00:10:0001/04/2007 00:11:0001/04/2007 00:12:0001/04/2007 00:13:0001/04/2007 00:14:0001/04/2007 00:15:0001/04/2007 00:16:00 If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute. Overall I'm trying analyze how many telephone lines we need Any Help much, much appreciated Thanks GW
I have tried various combination of Joins, but unable to get the desired result as the tables have many-to-many relationships, therefore I get many duplicate rows in the result. UNION will not solve the problem, as that will add the additional rows for the third table. Although I can achieve this by writing few lines of code, but I have to create a SQL Query for getting this result. Kindly tell me the way for creating the required Query for this. Many Thanks for your help.
Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.
now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.
Table Name: Story Table Columns: user_id, date, name, yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, address, city, state, zip, AorV (audio or video), filename, approved
I need to create two queries. One based on date in descending order, and one based on country_origin in alphabetical order. For both of these queries, the approved field has to = Y for yes, and when I return results I need it to return everything in the table EXCEPT for the name.
I'm a beginner at SQL queries so I was hoping someone could shed some light and help me??? Thanks a bunch!
ID, EntryDate and Comment are all easily returned from the [Journal] table. The problem I am having is with the Income and Expense fields. For the Income field, I want to SUM the Journal_Split.Amount fields where Journal_Splts.JEID = Journal.ID AND where Journal_Splits.Type = 1 (1 being the code for Income). The Expense field is just the same, except the Journal_Splits.Type will be equal to 2. When the query is done there should only be as many rows as there are in the [Journal] table, for the [Journal_Splits] table can contain up to 3 records for each [Journal] record. [Journal_Splits] just holds the individual amounts split out to different income/expense categories for a single transaction.
This to me has a lot of conditions that I can't even seem to build with access's query builder (yes I am using access for now.. will change over to sql at a later time).
Anyone have a good idea how to do this? Or have a better idea for structuring the database to make is easier to query? Right now I have it working, but instead of an Amount field, I have 1 Income and 1 Expense field, but since a transaction can only be either income or expense, one field will always be 0, which seems like a waste of space.
That may be a bit confusing so let me know if I can clarify. Much appreciated.
HelloI am using stored procedure with sql 2005 (with Visual studio 2005)I have two tables .. TABLE1 And TABLE2[color=blue]>From TABLE1 i need to retrive the OrderID's of the 4 most top rows. so[/color]i did:SELECT TOP 4 OrderID FROM TABLE1 order by OrderID descNow what i am trying to do is take the 4 row results (4 OrderID's) igot fromTABLE1 and check if the 4 rows (4 OrderID's) exist in TABLE2 for aspecificuserID i get by INPUT varible (@UserId)..What i want to return is only which OrderID'S existed in TABLE2 for thespecific user.If only 2 OrderID'S i retrived from TABLE1 exist in TABLE2 i willreturn only 2 OrderID's (so i can do my output in visual studio 2005using the reader())I would appreciate this if anyone knows how to do this sql query , isit possible to do this in 1 query? i want to put it in a storedprocedure.
Hi everybody,I have a table storing the quiz data of the students like this:Date | Time | Name | GradeI can show the data of specific student by the query below:SELECT *FROM tbl_quizWHERE name ='xxx'Suppose I have 10 records for each student, and I wanna display thedata like this:Previous quiz date | Previous quiz time | Date | Time | Name | GradeHow can I make the "Previous quiz date" and "Previous quiz time"? Howcan I query it? Please adviceFRANK
I am converting a date to a variable, then stripping out unwantedhyphens, making sure there is a 0 in the month/day, and chomping theyear to the last 2 digits. The end result is a 6 digit integer that Ican use in my SQL.05-31-2006 changes to 053106Everything works except chomping the year to the last 2 digits. Substrwould normally work, but my results are ommiting the substringcompletely.BEGINRIGHT('00' + CAST(DATEPART(mm, @date) as varchar(2)), 2 ) +RIGHT('00' + CAST(DATEPART(dd, @date) as varchar(2)), 2 ) +SUBSTRING(CAST(DATEPART(yyyy, @date) as varchar(2)), -2, 2)Any clues? Corrections?
Hello experts.I'm a novice sql writer and need some help in writing a query toextract applicable data from the following table (titled EMPLOYEE):--ID_NUMBERCODEDATE------------------ --------- --------12 VO2006091412 XD2006091312 AD2006091212 WR2006091112 AT2006091045 VO2006091445 XR2006091345 AT2006091245 AD2006091145 AT2006091078 AD2006091478 AT2006091378 VO2006091278 AD2006091178 AT20060910I need to select ID_NUMBERfrom EMPLOYEEwhere CODE = 'VO'caveat: I only want the ID_NUMBER(s) where the CODE = 'VO'and the previous CODE (by DATE) = 'AD'or the previous CODE (by DATE) = 'AD' with any CODE in betweenexcept 'AT';E.g., in the above example, the appropriate code should selectID_NUMBER(s) 12 and 78 because1. a VO code exists2. an AD code (by DATE) precedes it3. although 'AD' does not come immediately before 'VO' (in thecase of ID_NUMBER 12) 'AT' cannot be found in betweenI hope I haven't confused anyone. Any help would be appreciated.
I'm not sure if this is possible but what i want to do is as follows:I need toSelect all values in a table that are smaller then the preceding onetimewise or ID wise. Which ever.I have three columns. ID, timestamp and value.I'm using SQL server 2000. Is it possible to do this withou transactsql?ThanksMark
OK, first let me say that I am no DB person.But I have a user here who keeps getting this errorwhenever she does, whatever it is she does, withdatabases...A google search takes me to various forums whereI am forced to sign up before I can read any answers.Interesting note here is that the guy in the office nextto her can run this same query without any problems.So, can this be a hardware issue?If I threw more RAM at this problem would that do?Or is this strickly a coding issue?Why would one machine run this query ok, yet anothermachine generates this error?Any pointers that you guys could throw my way wouldbe greatly appreciated.Thanks*$
I have a commodity table and I would like to pull information from it based on the conditions. I can write code to pull straight forward information from the table but I need help in writing query such that the later columns are based on the former columns.
To clear my question, I am writing the sample code below. The column 4, 5 and 6 are based on column 1, 2, 3. Column6 is column3/column2 (which is simple)
But column4 & 5 are too complex for me to write the code for. Column4 is the % of Quantity (cty_code)/(all countries) for that particular commodity. cty_code correspends to a country.
Table information:
[dbo].[2005exp]( [dom_or_for] [char](1) NOT NULL, [commodity] [char](10) NOT NULL, [cty_code] [char](4) NOT NULL, [district] [char](2) NOT NULL, [stat_month] [char](2) NOT NULL, [cards_mo] [decimal](8, 0) NOT NULL, [all_qy1_mo] [decimal](12, 0) NOT NULL, [all_val_mo] [decimal](12, 0) NOT NULL )
Table data snapshot:
Query1: select top 10 commodity, sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp] where cty_code=5310 group by Commodity order by price desc
Output:
Commodity
Quantity
Price Column4 Column5 Price/Quantity
8517305000
0
46307629
8517905000
0
11990255
3003100000
2268
2687905 0.35% 29.92% 1185.1437
8524990000
148
1815000
8471300000
2591
1673570
9802400000
0
1560247
9880004000
0
1197407
8802300080
1
1100000
3819000000
601192
899417
9802200000
0
811996
Query2: (based on the results of query 1 i.e., commodity) select Sum(all_qy1_mo) as Quantity, sum(all_val_mo) as Price from [2005exp] where commodity=3003100000 Output:
This seemed like the most appropriate SQL forum for this question, if not, let me know where to move it!
I'd like to write a query (or perhaps a stored proceedure) to generate the following output based on this psuedo table definition:
Item-Table
ID Number Description
Detail-Table
ID ItemID Name Value
Now, imagine this record in the Item table:
ID = 1, Number = AB123, Description = AB Widget 123
With these records in the Detail table:
ID = 1, ItemID = 1, Name = Height, Value = 1.3 ID = 2, ItemID = 1, Name = Width, Value = 0.8
I need to return an Output table that looks like this:
ID = 1, Number = AB123, Description = AB Widget 123, Height = 1.3, Width = 0.8
So the generated output table should contain all the columns from the Item table as well as one column for each child record in the Detail table.
It is also possible for the selected items in the Item table to have varying Name records in the Detail table - so the Output table needs a column for each uniquie Name across all Detail records for all the given Item records.
Example:
Item Table:
ID Number Description 1 AB123 AB Widget 123 2 AB124 AB Widget 124
Detail Table:
ID ItemID Name Value 1 1 Height 1.3 2 1 Width 0.8 3 2 Height 1.5 4 2 Width 1.0 5 2 Color Blue
Output Table:
ID Number Description Height Width Color 1 AB123 AB Widget 123 1.3 0.8 DbNull 2 AB124 AB Widget 124 1.5 1.0 Blue
If this can be done in a query, great. If a stored proceedure is necessary, that is ok, but I may need a little more syntax help.
