Hi All,
I can do this in Access, with VB, but I'm pretty new to SQL Server.
Say you have the following table, call it TblStudents:
Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony
And you wanted a view that would give you:
Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony
How would you do this in SQL Server?
Thanks.
Henry
Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300
View 1 Replies View RelatedI have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.
Personstartdateenddate Money
7d3397/1/201412/31/2014 1000
7d3391/1/20145/23/2014 355
Hello,
I have a question, what does a statement look like that finds the duplicate rows and combines them,
I have a table named PRODUCTS in it 3 columbs Cost, Stock, Part_number.
I need to find all Part_numbers that dublicate, Combine the rows into 1 & combine (sum, add) their stock together is the new row & take an avarerage of their cost and use it as cost in the new row where they combine.
Please help me, I am stalled. Looked all over the internet & could not find anything, I really need this for a project I can not finish.
I have the following SQL statement:
SELECT part_number,
COUNT(part_number) AS NumOccurrences
FROM Products
GROUP BY Part_number
HAVING COUNT(part_number) > 1
I've seen a number of questions on combining rows, but not one
exactly like this. I have a solution, but I'd like to know
if there are other ways.
I'd like to select and combine rows from a table. Here's a simplified
version of the table:
tab1
key date status
1 1/1/06 stat1
1 1/2/06 stat2
1 1/3/06 stat3
1 1/4/06 stat4
2 1/1/06 stat1
2 1/2/06 stat2
And the desired results:
key date status prevstatus
1 1/1/06 stat1 null
1 1/2/06 stat2 stat1
1 1/3/06 stat3 stat2
1 1/4/06 stat4 stat3
2 1/1/06 stat1 null
2 1/2/06 stat2 stat1
Here's the simplified version of the solution:
select
a.*,b.status prevstatus
from
tab1 a
left join
tab1 b
on a.key = b.key and
b.date =
(select max(date) from tab1 c
where
a.key = c.key and
a.date > c.date
)
Is there a better way?
I have data that looks like this:
ID Value
1 Descr1
1 Descr2
1 Descr3
where Descr could range from 1 to 100 for each ID
The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank you
Hi,
I'm trying to do this in T-SQL. I have a query that returns, for this matter, only 2 rows. The table output looks like this:
AdjType
AdjNbrStart
AdjNbrEnd
AdjTotalAmt
1
9
9
-134180
2
8
10
104981.42
How do you do it to bring back only one row that will look like so:
1
9
9
-134180
2
8
10
104981.4
Is it possible? Like I said, I only have 2 rows. I'm not concatenating these columns, just want to bring it back as one row. Any ideas? Thanks.
I have the following query:
Code:
SELECT DISTINCT [WL].[Id]
,[WL].[UserId]
,[WL].[DIF]
,[WL].[MW]
,[WL].[Notes]
,[WL].[WDate]
[Code] ....
And the error i get is:
> Column 'Wsite.dbo.WLog.Id' is invalid in the select
> list because it is not contained in either an aggregate function or
> the GROUP BY clause.
What I am wanting to do is just conbine the data if there are more than one **UserID** in the list.
As an example:
Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 14 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5845| 99304 | .... | 99304| Bob Barker | 8 | ....
5846| 99304 | .... | 99304| Bob Barker | 11 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....
So it should look like this:
Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 33 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....
Notice that Bob Barker's SLength was combined (14+8+11=33).
I need some help with a query . I have two tables "config" and "item".
I have table config as follows:
ConfigID
ItemID
With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112
And table item as follows:
ItemID
ItemTypeID
ItemValue
With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789
So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID
Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL
How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC
Thanks for your help
I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?
Employee Name
John
Mike
Dale
I am working with a database derived from text documents. One of the tables (TEXT001) contains the text of the documents with each paragraph of each document assigned to its own row with a paragraph number in a SectionNo column. I want the entire text of each document in a single row with its own unique number (so that I can do a full text search with SQL Server 2005 that will search and return the entire document as a result). How do I combine the rows with the same DocumentID into a single row of text data? This will put the entire text content of each document in its own row.
TEXT001 table as it is
DocumentID
SectionNo
SectionText
1
1
Paragraph 1 of Document 1
1
2
Paragraph 2 of Document 1
1
3
Paragraph 3 of Document 1
2
1
Paragraph 1 of Document 2
2
2
Paragraph 2 of Document 2
New TEXT table
DocumentID
SectionText
1
Entire text of Document 1
2
Entire text of Document 2
I realize that I can use €œunion€? to combine tables with the same data type, but that is not what I am trying to do. Ideally, there is a way to create a new table and fill it with the combined SectionText data as a batch command. If anyone can tell how to do this, I would appreciate your help.
More modestly, I tried to use the €œGroup By€? clause to combine the SectionText data using this query:
SELECT DocumentID, SectionText FROM TEXT001
GROUP BY DocumentID
And got this error message:
Msg 8120, Level 16, State 1, Line 5
Column 'TEXT001.SectionText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I figured that I could not contain the SectionText data as an aggregate function since it is text data and cannot be €œsummed€?, so I tried including it in the GROUP BY clause:
SELECT DocumentID, SectionText FROM TEXT001
GROUP BY DocumentID, SectionText
And got his error message:
Msg 306, Level 16, State 2, Line 5
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Where do I go from here to accomplish my goal of combining the paragraphs of each document into one row per document?
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.
Sample Data:
WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'
)
SELECT *
FROM SampleData;
The results from the above are as follows:
PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate
The desired results would be:
PERSONDate Department Rate
123404/02/2014 123 80.0
How I could accomplish taking several rows for one account and concatenate them into one row, for example I have account_num, invoice_date, transaction_num, msg_counter,Message_2,SQL_LAST_UPDATE the special characters &,",!,$,# are used to determine the Message_2 content for a given account_number that are supposed to be together.
I am needing to put all of that accounts_messages in one row to display on a report, the table I am pulling this data from only has a varchar(40) for the message_2, a proprietary source so can't change that length, "I'VE ASKED THEM TO DO THIS, AND THEY REFUSED". So my only option is to insert this data into my table and create a single Message_2 for that account.
00000000332015-01-16 10:09:43.00000&19 confirmation so 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000"19ACCT 186743. HE SAID RADIO HAD 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000!19CALLED Carl ABOUT DEACTIVATION OF RADIO 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000$19FFERENT ACCT # YEARS AGO, BUT 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000'19I can cancel the (0.00) billing line on 2015-01-19 15:34:59.000
[Code] ....
I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.
Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.
EXAMPLE: All in one row.
ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.
I have attached an image of the SQL VIEW that I need to modified so it does this.
THE QUERY:
SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC
I am needing to combine the Notes field where Number and date are the same...For example
for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.
CREATE TABLE [dbo].[MyTable](
[NUMBER] [varchar](10) NULL,
[HD_DATE_TIMEX] [datetime] NULL,
[TRANS_NO] [varchar](2) NULL,
[MESSAGE_COUNTER] [varchar](1) NULL,
[Code] .....
I have a table of attributes set up as follows:
ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.
I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:
ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
Hi Im having trouble with this it seems simple enough but its not!
I have a source Table called Access_table example
Name Role1 Role2 Role3 Role4 Role5
a 1 0 0 0 0
a 0 0 1 0 0
b 1 0 0 0 0
c 0 1 0 0 0
d 0 0 0 0 1
e 0 0 1 0 0
e 0 1 0 0 0
f 1 0 0 0 0
g 0 0 1 0 0
I need to create a view that basically finds all the names with double Roles and merge the results into 1 row example.
Name Role1 Role2 Role3 Role4 Role5
a 1 0 1 0 0
e 0 1 1 0 0
I cannot change the information in the source table and the results need to be in a view as the roles will change. Every time I try and do this I duplicate the row again. Can anybody suggest a solution.
Thanks in advance.
Hi, i have a Stored Procedure that is calling a view. I need to pass on all the records of this view so i made another view that returns the rows count and then a for loop from 0 to count-1. But then how can i access the rows and columns of the view one by one? Any idea?
And if someone has a better logic that gives the same result please let me know. Thanks...
I have two tables that have a column called "subscriberid". I imported rows into the main table, and it's sister table.
There are 90,000 rows in the main table and 94,021 in the other table.
It appears that roughly 4,000 rows have disappeared in the main table, but not in the other table, and I don't understand why?
I'm new to SQL Server 2005 Express and I'd appreciate any help you can give me.
Thanks,
Bill
Hi, my name's John and this is my first post here at SQL team. I've learnt a lot from the forums here, never needed to post though. I'm pretty good at sql, but not that good and I think for the first time in couple of years I now have a query that I'm completely unable to create.
Let me explain the setup.
I've got three tables e.g.
Products (productid, Name)
100 Computer
200 Printer
300 PDA
ProductActions (ID, Name)
6 changed casing
8 Changed motherboard
7 replaced cd-drive
ProductPerformedActions (productid, actionid)
100,7
100,9
200,8
This basically shows a simple setup showing that product (computer) has been done the action "repaired" and product (printer) has been done the action "changed motherboard". The query i'd like to have is where I can select all products that have not been performed on cerrtain actions. e.g. a list of products that have not been performed on all actions, such as
UnPerformedActionsList(product,actionid)
PDA,6
PDA,7
PDA,8
Printer,7
Printer,6
Computer,8
I know its foundation is something like this:
select productid from products where productid not in
(select productid from productperformedactions....
which elimiantes products that have been performed an action on, but it also removes it for ALL actions,
which i'm trying to avoid. All i want is kind of a cross join, listing all actions, then inverting it, to show
all products that have NOT had it performed on them.
I hope this makes sense.
Thanks
John
Help, is something wrong with my SL Server? I am unable to return any rows from all tables in all databases (user and system)on My SQL 7.0 SP2 machine. Whne i right click on the table in E.M and select design or open table i get no results. Does anyone know why this is happening? It did not always happen either.
Thanks
I've been searching the forums and Google for some help splitting up rows in a table without any luck. I'm not quite sure what to even look for
I have a table is MSSQL 2000 that looks as follows:
Code:
id custnum b1_email b2_email b1_sub b2_sub
------------------------------------------------------------------------
1 123456 b1@host1.com b2@host1.com 0 0
2 654321 b1@host2.com b2@host2.com 1 0
3 321654 b1@host3.com b2@host3.com 0 1
Now... I am hoping create a view that splits these rows up so that only a single email address is on each row. I'd like to split it up as follows:
Code:
custnum email sub
----------------------------------
123456 b1@host1.com 0
123456 b2@host1.com 0
654321 b1@host2.com 1
654321 b2@host2.com 0
321654 b1@host3.com 0
321654 b2@host3.com 1
Any help would be great! I imagine some sort of join command can be constructed using a single table?
I have a table sorted by vendor, then item, then Status date with a QC Pass date and Fail date.
I need a SQL 2008 view that counts how many consecutive times a Vendor/Item has passed QC.
When it fails, the count resets to zero, then begins incrementing again.
I need to know how to generate the last column (Count).
I have tried using a ROW_Number() OVER(Partition BY, Order By...) command in the view, but I cannot seem to make it work right.
VendorItemStatusDatePassDate Faildate Count
10056322010-05-092010-05-091
10056322012-12-152012-12-152
10056322013-05-252013-05-253
10056322014-11-172014-11-174
[Code] ....
Hello,
Is it possible to insert rows into a view, update rows in a view and delete rows in a view?
If so, how does T-SQL handle it when a VIEW is the result of following query?
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 ON table_name1.col = table_name2.col
WHERE condition
Thanks in advance,
Erke.
I have one view which is based on couple of tables. Here is the definition of view. Which are the options i can use to optimize the view for better performance. This is one of the view which causing issue on database.
CREATE VIEW [dbo].[V_Reqs]
WITH SCHEMABINDING
AS
SELECT purchase.Req.RequisitionID, purchase.Req.StatusCode AS Expr2, purchase.Req.CollectionDateTime,
purchase.Req.ReportDateTime, purchase.Req.ReceivedDateTime, purchase.Req.PatientName, purchase.Req.AddressOne,
purchase.Req.AddressTwo, purchase.Req.City, purchase.Req.PostalCode, purchase.Req.PhoneNumber,
[code]....
I have a table like below:
ItemIdAmountTax1Tax2SrvType
111 100 10 20 1
112 200 10 2
113 300 10 30 3
Now I want to create View that will have an exploded resultset based on SrvType.
For SrvType 1 and 2 there will be 2 lines per Itemid - One for 'Amount' anod another for 'Tax1+Tax2'. But for SrvType 3 there will be 3 lines per 'ItemId' - one for 'Amount', one for Tax1 and another for 'Tax2'.
I have a few hundred source records like this. Now sure how to achieve the exploded resultset with a View.
I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.
Select
Dimension.Age_Band.[10_Year_Age_Band],
Count(*)
From
Fact.APC_Episodes
Inner Join Dimension.Age_Band ON
Fact.APC_Episodes.AGE_BAND_SKEY = Age_Band.AGE_BAND_SKEY
Group By
Dimension.Age_Band.[10_Year_Age_Band]
I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.
Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View Related