How To Synthesize A Single Row Result From A Cross-referenced Table?
Jan 13, 2008
Hello, I have an SQL problem that is hard to describe so here's an example scenario:
a) company has a personnel database, identifying each person in the company and their assigned role(s).
b) tblPersonnel has a row per person, key is 'PersonnelID'
c) tblRoles is a list of all the roles in the company (administrator, clerk, shipper, truck-driver, etc.), key is 'RoleID'
d) tblPersonnelRoles is a cross-reference of all the roles assigned to each person - in simple format of:
PersonnelID | RoleID
Given all the above, I need to have a select statement that produces one row per person, and list all the fields from tblPersonnel AS WELL AS identifies each role assigned to the person.
I'm no SQL guru .. is this even possible?
I know there are alternative solutions (e.g., to write code that for each Personnel does a lookup in Roles) but for one particular situation I need a single SQL statement, or even Stored Procedure.
Thanks in advance,
Rick Piovesan
Detaya Corp
Aurora, Ontario, Canada
I have information on clothes in a table that I want to select out to a result set in a different structure - I suspect that this will include some kind of pivot (or cross-join?) but as I've never done this before I'd appreciate any kind of help possible.
Current structure is:
Colour Size Quantity ----------------------- Red 10 100 Red 12 200 Red 14 300 Blue 10 400 Blue 12 500 Blue 14 600 Green 10 700 Green 12 800 Green 14 900 Green 16 1000
I want to produce this result set:
Colour Size10 Size12 Size14 Size16 ------------------------------------- Red 100 200 300 0 Blue 400 500 600 0 Green 700 800 900 1000
There could be any number of sizes or colours.
Is this possible? Can anyone give me any pointers?
Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am migrating data. I found a strange thing in the existing table, there is a column named workshopCaseID in a TruancyCase table ,The datatype for workshopCaseID is an int (null). it is a FK, there are some records are 0 values, but the fk referenced primary table only have 1-8 values, how are the 0 values get inserted there?
The parent table has the column OrderID which is the primary key. There is a unique non-clustered index in the same table with the same column and some included ones. A child table references the parent table on the OrderID column. Unfortunatelly, in the sys.foreign_keys table the index that has been used to acomplish the referential integrity is the unique non-clustered one instead of the primary key (which is the clustered index as well).
This is causing issues in some maintenance tasks where we need to disable all the indexes of the table apart from the clustered index. Disabling these indexes leeds to disabling the not properly configured foreign key as well. Maybe dropping the index create the constraint again and then recreate the index is a workarround but I wish to have something more elegant and future proof.
I have a "case" table describing what type of social worker case and who performed work on the case. Four case workers are referenced within each case and all workers belong to the "worker" table. I am trying to present the case worker name on a detailsView rather then the much simpler task of showing the worker's index stored within the case. I can't seem to get the correlation names working.
SelectCommand="SELECT [ReportID], [RecCreated], tblVCaseType.Description AS cType, [IntakeDate], tblVCounty.Description AS County, [Abuse], [Neglect], [Dependency], [AfterHours], [Screenout], tblVScreenOutcome.Description AS Outcome, [Closed], [ClosedDate], iWorker.[Worker Name], cWorker.[Worker Name] , tblVAssessmentType.Description AS aType, tblVTimeframe.Description AS tFrame, tblVDisposition.Description AS Dispo , tblVProgramXfer.Description AS Xfer,
[Code] ...
Getting only one worker name is easy. But, I need all four. I have tried many versions without success. The error from this version is repeated 4X:
The multi-part identifier "tblVWorker.ID" could not be bound.
Am working on sql database table shifting to new database tables. Am getting the following error while truncating the tables:-
Cannot truncate table 'orderItem' because it is being referenced by a FOREIGN KEY constraint.How can i remove all the items in the table if i found this error.
I know that TRUNCATE TABLE can be used to reset the identity counter, but it can't be used on a table referenced by a foreign key. Anybody knows how to do that? Thanks.
hello i am a beginner i it possible to get this result by single query ?
i got a int column and a datetime column is it possible to get the sum(int column) for all the year 2007 and by the april2007 and by the month n year by the person name Tony
I have a table with 1 column containing numeric values, and I need to create a query which displays the count of values for each set of conditions. For example, if the table contains the values 1,2,3,4,5 and 6, the output of the query should look like this:
L M H ----------------------- 3 2 1
(L are values below 4, M between 4 and 5, H is 6 and above)
I am using 3 tables: projects, project_manager and project_employee
projects
- project_id (int, PK) - project_name
project_manager
- project_id (int, PK) - manager_id (int, PK)
project_employee
- project_id (int, PK) - employee_id (int, PK)
Assume that a manager is currently logged in (so we know what is his ID), what I trying to do is write a query that will display a grid-view showing:
project_id, project_name and number of employees in project.
I tried these quires to test:
SELECT project_id, count(emp_id) as NumberOfEmployees into #tempTable FROM project_employee WHERE project_id IN (SELECT project_id FrOM projects) GROUP BY project_id
SELECT projects.project_id, projects.project_name, NumberOfEmployees FROM projects, #tempTable WHERE projects.project_id = #tempTable.project_id
Problems are: 1 - I used two queries not one 2 - The result is for all projects of all managers, I just want to display projects for a specific manager (for example mag_id = 5) 3 - it created a temporary table - which I dont want.
Is there anyway to get what I want in a single query?
I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this: 1. SELECT name FROM table1 2. SELECT name FROM table2 3. Join the two results together and order them alphabetically 4. Return the result set I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
CREATE Table #Table1 ( ID INT, Name VARCHAR(50), Class VARCHAR(10) ) INSERT INTO #Table1 Select 1, 'name1', 'a' UNION ALL Select 2, 'name1', 'a' UNION ALL
[Code] ....
Is it possible to have each name and its corresponding class in a single line separated by commas to give a result like the one below in #table2 ?
CREATE Table #Table2 ( ID INT, CommaSeparated VARCHAR(100) ) INSERT INTO #Table2 Select 1, 'name1, a' UNION ALL
[Code] ...
What I have
Select * FROM #Table1
Final Result Select * FROM #Table2
Note that I still want to see all the IDs regardless.
If that is not possible to see all the IDs, I think the results below in #Table3 should suffice.
CREATE Table #Table3 ( CommaSeparated VARCHAR(100) ) INSERT INTO #Table3 Select 'name1, a' UNION ALL Select 'name2, b, c, d' UNION ALL Select 'name3, e, f' Select * FROM #Table3
In SQL Server SELECT query Result displays tablePlease tell how to create stored procedure for a single row at a time i.e. all only ONE row is displayed at a time exselect name from useswhere name is display is tabular format,but i need in row with coma anc,d,f,f,e,g,h,jin this wayhow can i write a stored procedure for that
I set up a connection to mysql using ADO.NET's ODBC Data Provider. And I'm running a simple query to return one table's maximum ID(int32 unsigned).But when I bind the result to a variable and excute task. It gives out error message: "An error occurred while assigning a value to variable "MaxAuditLogID": "Result binding by name "MaxID" is not supported for this connection type. " I tried to change the type of variable around but with no luck.
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday') --- DECLARE @ITEM_ID uniqueidentifier DECLARE ITEM_CURSOR CURSOR
I set up a connection to mysql using ADO.NET's ODBC Data Provider. And I'm running a simple query to return one table's maximum ID(int32 unsigned). There is no problem to achieve that.
But when I bind the result to a variable and excute task. It gives out error message: "An error occurred while assigning a value to variable "MaxAuditLogID": "Result binding by name "MaxID" is not supported for this connection type. "
I tried to change the type of variable around but with no luck.
select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/11/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/12/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/13/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/14/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/15/2006' group by task_date union select convert(datetime,task_date) as date, sum(Case status_id when 1000 Then 7 else 0 end) as Programming, sum(Case status_id when 1016 Then 5 else 0 end) as Design, sum(Case status_id when 1752 Then 4 else 0 end) as Upload, sum(Case status_id when 1032 Then 2 else 0 end) as Testing, sum(Case status_id when 1128 Then 1 else 0 end) as Meeting, sum(Case status_id when 1272 Then 1 else 0 end) as Others from task_table where user_id='EMP10028' and task_date='9/16/2006' group by task_date
My out put is Date Program Design Upload Testing Meeting Others 2006-09-11 00:00:00.000 42 0 0 8 2 1 2006-09-12 00:00:00.000 77 0 0 4 0 0 2006-09-13 00:00:00.000 56 0 0 8 0 1 2006-09-14 00:00:00.000 63 0 0 6 0 1 2006-09-15 00:00:00.000 63 0 0 6 0 1
Now i want in below format 2006-09-11 2006-09-11 etc
I have the following structure with remote select permissions; I cannot create temp tables or use stored procs:
tblEvent with event_pk, eventName tblReg with reg_pk, event_fk, person_fk, organization_fk
I'm currently using a case statement to get counts for these categories: case when c.person_fk is Null and c.organization_fk is not null then 'Employer' when c.person_fk is Not Null and c.organization_fk is null then 'Individual' when c.person_fk is not Null and c.organization_fk is not null then 'Both' else 'Unknown' end
But I need some kind of count (0) for every category. I've used a cross-join, group by in the past - but what do you do if you don't have a table? For example, the end result when selecting event_pk=(112,113) would be:
From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF
XSection (Width, Height, Flange, Leg, LegCount)
I tried the following to get a pivot table but it does not give a single row but 5.
SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width, CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height, CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange, CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg, CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp ON tsp.fkProperty = sp.Property WHERE tsp.fkTemplate = 1
So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?
Hi all,I have a table in this formatcolname1 colname2 colname3col1data1 col2data1 col3data1col1data2 col2data2 col3data2col1data3 col2data3 col3data3col1data4 col2data4 col3data4I want to display it in this formatcolname1 col1data1 col1data2 col1data3 col1data4colname2 col2data1 col2data2 col2data3 col2data4colname3 col3data1 col3data2 col3data3 col3data4Basically rotate it through 90 degrees clockwise and flip it over :)I'm pretty sure this is done by using a crosstab query and or aderived table or temp table. The problem is I use a crosstab query toget the original data into the first format. I've been strugglingtrying to get the ouptput into the second format for over a day nowand just can't seem to get it to work. Can anyone give me any pointerson the general solution to this?I hope this makes sense. Thanks for the help.
Hi All, The problem is about cross reference. 1. I have a third party cross reference store procedure SimpleXTab CREATE PROCEDURE [dbo].[SimpleXTab2] @XField varChar(50), @XTable varChar(100),@XWhereString varChar(250), @XFunction varChar(10), @XFunctionField varChar(50), @XRow varchar(300),@ResultTable varchar(100) ASDeclare @SqlStr nvarchar(4000)Declare @tempsql nvarchar(4000)Declare @SqlStrCur nvarchar(4000)Declare @col nvarchar(100) set @SqlStrCur = N'Select [' + @XField + '] into ##temptbl_Cursor from [' + @XTable + '] ' + @XWhereString + ' Group By [' + @XField + ']' /* select @sqlstrcur */exec sp_executesql @sqlstrcur
declare xcursor Cursor for Select * from ##temptbl_Cursor open xcursor Fetch next from xcursor into @Col While @@Fetch_Status = 0Begin set @Sqlstr = @Sqlstr + ", " set @tempsql = isnull(@sqlstr,'') + isnull(@XFunction + '( Case When ' + @XField + " = '" +@Col + "' then [" + @XFunctionField + "] Else 0 End) As [" + @Col + "]" ,'') set @Sqlstr = @tempsql Fetch next from xcursor into @Col End /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */ set @tempsql = 'Select ' + @XRow + ', ' + @Sqlstr + 'into ' +@ResultTable+' From ' + @XTable + @XWhereString + ' Group by ' + @XRowprint @tempsql set @Sqlstr = @tempsql Close xcursor Deallocate xcursor set @tempsql = N'Drop Table ##temptbl_Cursor' exec sp_executesql @tempsqlprint @tempsql /* Select @Sqlstr as [mk], len(@sqlstr) as [leng] */print @sqlstr exec sp_executesql @Sqlstr if @@rowcount = 0 select 'No Records found'GO 2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function. how can i modify SimpleXtab to avoid using math function but still can generate cross reference. exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
I am currently building a website to deal with different product information and sales with php. I am using SQL to sort the database and pull out information.
The final thing i need to do is work out the total revenue of each product however the problem i am having is that the 'Price' column and 'SalesVolume' column are in two different tables and they need to be multiplied together.
The two tables and column headings are as follows:
Product ID Name Price
MonthlySales ID ProductCode Month Year SalesVolume
(ID and ProductCode are linked together in a relationship)
I cannot see anything wrong with the syntax in my query however i believe there is.
Here is the query I am using:
Code: "SELECT SUM(Products.Price * SUM(MonthlySales.SalesVolume)) as revenue FROM Products INNER JOIN MonthlySales ON(Products.ProductCode = MonthlySales.id) GROUP BY Products.ProductCode";
Is it possible to create cross table query via SQL Express 2005 since it's possible to do with MS Access?
Sample query in MS Access:
TRANSFORM Count([tip]) AS [The Value] SELECT [sifra], [naziv] FROM naselja GROUP BY [sifra], [naziv] PIVOT [opstina] Is there any soultion how to make same or at least identical SQL query expression which will behive like MS Access ones?
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status] ,[final uplift status], [final rank], [final uplift percentage] ,practiceID=row_number() over (partition by [practice unit name] order by Metricname) ,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
