creating a query to group identical rows into one and placing corresponding data in appropriate columns for a table named items, what I mean is that I have a table structured as below
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:
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london where paris where toronto
why us why japan why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as ( select cq.ConditionalQuestionID from ConditionalQuestions cq inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y') select distinct q.Question, a.Answer from QuestionAnswers qa inner join Answers a on a.AnswerID = qa.AnswerID inner join Questions q on q.QuestionID = qa.QuestionID inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts FROM log_directory_contacts GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer): user1 answer1 answer2 answer3user2 answer1 answer2 answer3 How can this be done? How can all answers of a user appear on a single row Thanx,Danny.
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I am rather new to reporting on SQL Server 2005 so please be patient with me.
I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.
For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report
The table (System Info) looks like:-
ID | System part | 1 | RAM 2 | Disk Drive 10| CPU 11| CD ROM |
Which
So basically I need it to look like this.
Name | IP | RAM | Disk Drive| ---------------------------------------------- A | 127.0.0.1 | 512MB | Floppy
So Far my SQL code looks like this for 1 item SELECT SYSTEM PART FROM System Info WHERE System.ID = 1
How would I go about displaying the other system parts as columns with info
I have an Parent table (Parentid, LastName, FirstName) and Kids table (Parentid, KidName, Age, Grade, Gender, KidTypeID) , each parent will have multiple kids, I need the result as below:
I previously posted a problem with result set bindings but I have not been able to resolve my problem. I guess all this comes with being new to programming in this environment! Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns. I have to read and manipulate each row. I was originally looking at using a foreach loop but have not been able to get it to work. One reply to my previous thought I should be using a data task to accomplish this. Could someone tell me the best way to handle this situation? As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me! Thanks in advance!
Hi,I'm trying to build a query that get only one row from a group ofrows, but I need the values from that row and not the results of onefunction group.I need one row for each idRef, with column2=2 and the bigger column1id |idRef | column1 | column21 1 0 12 1 1 23 1 2 14 2 0 15 2 1 26 2 2 17 2 3 2For these, I will take the rows with id=2 and id=7.Thank you, and sory for my english.
Okay, I am sure this is an easy question, but for some reason I cannot wrap my brain around it. I have a table that has data similar to the following:
IPAddress VisitDate URLVisited
192.154.21.554 9/18/2007 http://www.microsoft.com
192.154.21.554 9/19/2007 http://www.google.com
164.21.124.23 9/19/2007 http://www.microsoft.com
192.154.21.554 9/20/2007 http://www.yahoo.com
What I am trying to do is use a select query to find the most recent visit per IP address (but I also want the other row data like URL visited). I have something like this that is finding the most recent visit per IP address:
SELECT IPAddress, MAX(VisitDate) AS MaxDateVisited FROM VisitorTable GROUP BY IPAddress
How do I also pull in the URL that is associated with the MAX date visited? Thank you!
Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg
Row A = NoteID 1, FK_JobID 1, UnRead
Row B = NoteID 2, FK_JobID 1, UnRead
Row C = NoteID 3, FK_JobID 1, UnRead
I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.
I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.
Heres my attempt
DECLARE @UserID INT
SET @UserID = 4
SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact, Master_Jobs.Job_Title, Master_Jobs.JobID FROM User_Notes INNER JOIN Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID
WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND BeenRead = 'UnRead'
As you can see each room is assigned and id and once the room changes the id starts from 1 again. I don't want the 2nd row to appear because 9am - 1pm is in 8am - 5pm. How do I remove this row?
My first post on the forum, I wish I had found this place sooner, looks to be full of good advice and knowledgeable posters.
I have tried searching and looking at the FAQ's but couldn't find an answer. So here goes, all help greatly appreciated.
SQL Server 2005
Table (many columns left out for simplicity)
ID - unique. HdrID - key to the header record. PTtimestamp - date, date/time the row was written to DB. PType - integer, representing various states of the row. etc etc etc (another 15 columns of data)....
Typically there is approximately 250 records per HdrID.
I am trying to do an SQL UPDATE without using my usual solution of writing vb code!
I want to update the PType of latest row (as per timestamp) for each HdrID to -9999.
I built a report with one field as a group. I want to count the number of rows in each field so I can add it to the group field or somw where in the report.
How can I count how many rows do I have in each group?
Hi everyone: I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting? SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2 This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all. Thanks for any help.
hi, i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit FROM UserVisits WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL) GROUP BY UserName UNION SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit FROM UserVisits AS UserVisits_1 WHERE (ProductID = @ProductID) AND (UserName IS NULL) GROUP BY AnonimIP this will return something like: zuperboy90 - 4 visits ANONIMOUS - 6 visits 85.104.103 - 2 visits etc how can i count the rows returned in both selections (4+6+2 = 12) ? thank you
