Query Help: Need At Least One Row For Each Value In Each Of Two Columns
Jul 20, 2005
The purpose, as you can probably guess, is to produce a set of sample
documents from a large document run. The data row has a CLUB column and an
IFC column; I want a set of samples that contains at least one of each CLUB
and at least one of each IFC, but no more than necessary.
Example schema and data:
CREATE TABLE mDATA (
ID INTEGER,
CLUB CHAR(7),
IFC CHAR(4)
);
INSERT INTO mDATA (ID,CLUB,IFC) values (6401715,'AARPRAT','IC17')
INSERT INTO mDATA (ID,CLUB,IFC) values (1058337,'AARPRAT','IC17')
INSERT INTO mDATA (ID,CLUB,IFC) values (459443,'AARPPRT','IC25')
INSERT INTO mDATA (ID,CLUB,IFC) values (4018210,'AARPBAS','IG21')
INSERT INTO mDATA (ID,CLUB,IFC) values (2430656,'AARPBAS','IG21')
INSERT INTO mDATA (ID,CLUB,IFC) values (6802081,'AARPPRD','IG29')
INSERT INTO mDATA (ID,CLUB,IFC) values (4236511,'AARPPRD','IG29')
INSERT INTO mDATA (ID,CLUB,IFC) values (2162104,'AARPBAS','IG21')
INSERT INTO mDATA (ID,CLUB,IFC) values (2073679,'AARPPRD','IG29')
INSERT INTO mDATA (ID,CLUB,IFC) values (8148891,'AARPBAS','IG21')
INSERT INTO mDATA (ID,CLUB,IFC) values (1868445,'AARPBAS','IG21')
INSERT INTO mDATA (ID,CLUB,IFC) values (6749213,'AARPBAS','IG21')
INSERT INTO mDATA (ID,CLUB,IFC) values (8363621,'AARPPUP','IG29')
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005 Business Logic Tier: Views, User Defined Functions, Stored Procedures Data Access Tier: Stored Procedures Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies different number of layers within a hierarchy select a organization or select All of the organizations with the organizational hierarchy combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies: Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country) Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams Country = "USA", Work Group = all work groups
How to implement the data interface (Stored Procs) to the Reports Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all") (WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
Case: Exporting Report to PDF/Printing/TIFF Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
Hi All,I am new to Sql and I want to know if a given value lets say its 225is within ranges defined in the table.I have a table TblControl. Data type of both field is int.StartRange End Range0 100200 300500 600900 950(Sql server 2000 on window 2000)Thanks-Max
I have some data in week1 of the database and some data in week2 of the database. Now I want to add these both datas and put it in other column. Can I get exact SQL query for this??
Can someone Please help me with what seems to me a "complex" query??
I have the following columns/data - See Original Data.jpg
This is what I need the SQL query(s) to do. I need it to 1) Select the distinct names AND months 2) Count the number of times that a name AND a month appeared together 3) Sum totalsold for each name/month 4) sum employeescore for each name/month
Hi, I have 3 tables: Table SLA: SLA_code, SLA_name Table industry: Industry_code, Industry_name Table14: SLA_code_origin, SLA_code_destination, Industry_code, Freq
I would like to query to get in one table: SLA_name for DESTINATION, Industry_name, Freq
Note: SLA_code contains the same codes as SLA_code_origin and SLA_code_destination. All are the same codes in column oringin means the From point and colum destination the To point.
How can I query to get the Freq and Industry for those destination SLAs?
I have make a relationship between: Table SLA.SLA_code with Table14.SLA_code_destination and Table_industry.Industry_code with Table14.Industry_code
But I am not sure if just a SELECT SLA.SLA_name, Industry.Industry_name, Table14.Freq FROM SLA, Industry, Table14 would select the SLA_destination and not the SLA_origin.
I created the following simple query between two tables:
SELECT vru.User_Name0, vru.Full_User_Name0, vru.department0, vru.employeeNumber0, vru.title0, vru.manager0, vrs.location0, vrs.Netbios_Name0, vrs.Last_Logon_Timestamp0 FROM v_R_User vru LEFT JOIN v_R_System vrs ON vru.ResourceID = vrs.ResourceID ORDER BY vrs.User_Name0
I don't know how to properly insert a pic, but basically I get output, except the last 3 columns. I've tried every variation of JOIN and I cannot seem to solve this issue.
I know this is no problem for anyone who has ever done a bit of programming in SQL, but for me it is a problem because I'm rather new in SQL. My question is: how to copy column in the same table in query? I need move one column to another and then to delete the first column (but not the whole column, just one part so I can't use Copy - Paste in Enterprise Manager). I know how to select the part that I want, all I need now is to copy columns.
I just want to query all records with the maximum value in column invoices.numbers. So for instance I have records with invoice numbers = 850, 849, 800 respectively. I want only records 850 (the highest value). Then next month I want invoices with value 851. Anyone know how to write this easily so I don't have to write it out everytime, e.g. invoice.numbers=851?
Hi All, I have a simple question. If I have a view that query from joined multiple tables and return a lot of columns (more than 100). If I run a simple query just return several columns (e.g. 4-5 columns), will SQL Server query all columns first from all joined table? or can SQL Server query only necessary column from related table?
Does anyone have idea how to join table that can reflect both left and right join? Table A Table B Column1 Column2 Column3 Column4 Column1 Column2 Column 3 Column5 A Jan 5 xxx A Jan 1 yyy B Feb 3 C Mar 4 B Mar 4 C Apr 3 C Apr 2 D May 2 E Mar 1
Result Table Column1 Column2 Column3 Column4 Column 5 A Jan 6 (= 5+1) xxx yyyy B Feb 3 B Mar 4 C Mar 4 C Apr 5 (= 2+3) D May 2 E Mar 1
So the result table is a join on column1 and column2 (both are primary key), with column3 is a sum aggregate. Table A has additional column4 and Table B has additional column5, so quite difficult to union (In fact, there are a lot of column differences between table).
I have a sample table as below: (All columns do not have to have unique values) Column n is int, the rest are varchar [First row is the column Names]
n | t1 | t2 | t3 | t4 1 | a | b | c | d 3 | e | w | a | t 3 | w | q | u | k 2 | g | w | q | n 2 | a | b | s | a 5 | n | a | w | h
I need to create a query or some how create a way to get the data in the form of the below table:
n | t1 | t2 | t3 | t4 | t11 | t21 | t31 | t41 1 | a | b | c | d | 3 | e | w | a | t | w | q | u | k 2 | g | w | q | n | a | b | s | a 5 | n | a | w | h
What that means is that I have a huge table (~6000 rows) that has the column 'n' above (int type) which can have rows that start with the same number (such as number 3 & 2 above). I need these duplicate rows of numbers to be converted/transformed to columns alongside each other. This would create 1 entry for each type of number but have the 4 columns of t1, t2, t3, t4 'added' on as extra columns in the new table with a different column name ofcourse. So what that would mean is that if there were 3 rows of number n=2 then would have 3x4=12 columns, plus the 'n' column and so forth.
I am not sure on how to start the query for this. What I have thought is maybe using a cursor and going through the table. But not sure how I could do that really. The other is using a Pivot in a SELECT statement. But that would not work since the columns can grow indefinetely depending on how many of the same 'number' row there is!
I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC
I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table Any help would be great, thanks
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
Hi,I have a empskill Table which has 3 Columns (1) EmpID (2) SkillTypeID and (3) CourseID.Now SkillTypeID column has data 1 or 2 .......in here 1 means Primary Skill and 2 means Secondary Skill. CourseID will reflect the ID's of subjects like c#,SQL,etc I need a Query which will count the number of primary skilled and number of secondary skilled persons based on subject and will display as followsCOURSE ID SKILL TYPE 21 222 123 424 1IN SHORT: I want to count the number of particular skill types and display them against their respective course id. You need to display one column data of database into two columns as output. Hope you people will help me in this regard.Thanks in advanceRameshRaj
Hi,I guess my brain stopped but I need help with this. create table #RETURN( uid int, cid int, serviceid int ) insert into #RETURN select 44,75,2 insert into #RETURN select 44,76,1 insert into #RETURN select 44,77,3 insert into #RETURN select 45,78,3I need to query this table that will get me a distinct UID with matching CID ordered by serviceid from lowest to highest.The solution should look likeuid cid44 7645 78Thanks
Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.
Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.
I am wanting to make a query to find out if there are any columns in a table that are null for all records.
So far I am using a cursor that holds all the column names of that table and then a stored procedure that would use a loop to cycle through all the column names in a query to essentially count the number of nulls for each column and then an If statement to check to see if the #of nulls = to the number of records.
What isn't working is my query to count the number of nulls...Here is my query
select count(*) from XYZ where @cName is null
Ive tried declaring cName as a sysname, and varchar, neither work.
Also, I've tried using COL_NAME(OBJECT_ID('XYZ'), someColumnIndx)...I think this would be the better choice just because I wouldn't need to use a cursor, but both aren't working. I am a bit new to MSSQL and don't know my way around the system tables. Is there a sys table that would give me this info so I wouldn't need to perform a query?
I'm trying to create an Insert query and I'm having difficulty in 2 areas:
First, I would like to CAST/CONVERT a single column of the several columns in the tables below. Is it possible to retain the asterisk identifying all columns and single out a particular column to be converted as opposed to writing out each individual column in both the INSERT and SELECT statements? I would like to CONVERT the column "MILL_COST" from VARCHAR(50) to Money.
INSERT INTO ITEM_MASTER SELECT * FROM ITEM_MASTER_TEMP
Second, I've tried the following"conversions" in the SELECT statement, to no avail:
CONVERT(Money, MILL_COST) As MILL_COST CONVERT(Money, CONVERT(Varchar(50), MILL_COST) CAST(MILL_COST AS Money)
I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.
Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:
Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?
Could somone advise on how to change Names of Columns in a table via a script rather then Enterprise Manager. I have lots of columns (need to do it in bulk.) called like SUM(abc1...etc), which I can not change. This upsets the Business Objects because it thinks it needs a GROUP BY statement. I know in MapBasic there is something like " Alter Table "tablename" ( rename Date Date_of_Day ) " Is there an equivalent for TRANSACT SQL?. Please help, (and Happy Xmas!) Gezza
Hi,I tried to create a simple view as followsCREATE VIEW V_ALL_USERTABLE_COLUMNSAS(SELECTOBJ.NAME as TableName,COL.NAME as ColName,TYP.NAME AS TYPEFROMSYSOBJECTS OBJ,SYSCOLUMNS COL,SYSTYPES TYPWHEREOBJ.TYPE = 'U'AND OBJ.ID = COL.IDAND COL.TYPE = TYP.TYPE)Combined with consistent naming conventions I will use this view toeasily find foreign keys; a laSELECT *FROM V_ALL_USERTABLE_COLUMNSWHERE ColName LIKE ('%user_id')There is something wrong with my view definition that I don't getthough; it doesn't return all the columns. I have a table with thefollowing definitionCREATE TABLE [dbo].[c_messages]([cid] [int] IDENTITY (1, 1) NOT NULL ,[touser_id] [int] NULL ,[tosession_id] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[fromuser_id] [int] NOT NULL ,[message] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[message_read] [bit] NOT NULL ,[logout] [bit] NULL) ON [PRIMARY]GOThe problem is that the select I used to define the view doesn'treturn the touser_id column. I have sort of a sneaking suspicion thatthe problem has to do with joining syscolumns.type to systypes.type,but I don't know what to do instead (I'd really like to include thetype; it's useful if I ever changed the type of a primary key and wantto check that I also changed all the foreign keys).Any help would be appreciated!
Even though I select "Column Names" in Design View when creating a query (or view), only "* (All Columns)" appears in the table box.
In InfoPath, when I connect a combo-box, err drop down box, to the database, I am unable to connect directly to a table... no tables are shown. If I select a different database, these problems do not exist.
I can not find any setting to allow these columns to be shown in the design view or any setting that will "expose" the tables in InfoPath.
I tried creating a new database and exporting the data, tables and data, from the troubled DB to the new DB; however, the new DB exhibited the same behaviour. The system tables, Master and Model, have the same behaviour. Please help me with your ideas and suggestions... thank you very much for your time.
This database was upsized from Access 2003 to SQL Server 2000 SP4.
