Is it possible to roll up all child records into one comma delimited field? For example, if the parent table is league info, and the child table is team info, I would be looking to return results like this:
Field 1 Field 2
NFL Bears, Browns, Saints, etc
NBA Bulls, Celtics, Lakers, etc
TIA,
Rob
Hi,
I have the following table
JobID JobType JobName ParentJobID etc...
12345 Disconnection MyJob1
98475 New Pilot MyJob2 12345
85746 Streetlight MyJob3 98475
etc
I need a query that returns all the jobs that relate to a specific job
e.g if I pick 12345 I get the following:
12345 Disconnection MyJob1
98475 New Pilot MyJob2
85746 Streetlight MyJob3
As they are all related records.
Can anyone help
Thanks
I am trying to get a "Zero Values" report. The following code works except when there is no child row.
declare @m table
(
parcel char(1)
)
insert @m values ('A'), ('B'), ('C'), ('D'), ('E')
declare @v table
[Code] ....
I need the output to be:
B 0
D 0
E 0
When creating a new row or record in the Master table (by an Adapter update), how do I then populate new records in 15 different Child tables?
These Child tables are attributes of the Master table. I am sure there is more than one way to do this...
Thanks
Hi
I need to read a file and write parent/child relationship rows. I cannot seem to fugure out how I can generate keys that I an use for the relationship.
I looked at using a variable, but have no joy - I cannot instantiate the variable and it errors - notsure wy.
I also looked to see if I can write to a DB table that maintains key id, but not sure how to do that.
I alo thought of setting up the parent table key to be auto-increment,but canot see how I can read this so that I can us it in the child row inserts.
Help will be really appriciated
Thanks in advance.
My Question: Does anyone know of a decent way (i.e. I do not want to loop to insert each row and check the SCOPE_IDENTITY() field or anything like that) to copy parent/child rows to their same respective table besides using the method I have listed below (my manager does not really like the idea of the "PreviousID" field)? More details are listed below.
My Table Situation: I have a parent table and a child table. Both tables have an identity column as the primary key. The relationship between the tables is established using the parent table's primary key to the column in the child table that stores the relationship. The identity column in both tables is the only column that is unique in the tables.
Sample Data (made up and simplified for visualization purposes):
ParentTable - "Items"
ID ItemCategory Price
1,T-Shirt,$20
2,Blue Jeans,$50
3,T-Shirt,$40
ChildTable - "Components
ID ItemID Component
1,1,Fabric
2,1,ScreenPrinting
3,2,Fabric
4,2,Zipper
5,3,ThickFabric
6,3,ScreenPrinting
7,3,Elastic
My Need: I need to make a copy of the records (keeping the parent/child relationship intact) to the same table as the source records. For example, in my data example above, I may need to make a copy of all the "T-Shirt" items and their child records. As the parent records are copied, they will be assigned new keys since the primary key is an identity. Obviously, this new key needs to be used when creating the child records, but I need to somehow associate this new key to the new child records.
Possible Solution: I know this can be achieved by adding another column to the parent table to store the "PreviousID" (INT NULL). Using this new field, when I want to copy the "T-Shirt" items, I would insert the new records and store the ID of source records (i.e. the identity value of the row that was copioed would be stored in this "PreviousID" field). Once the parent record has been copied, I could then insert the child records, and I could join on the "PreviousID" field to get to the new ID to use for inserting the copies of the child records.
Thanks for reading this and for any help offered.
I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help.
Select
CustomerCorrection.Id As ParentId,
cc_company As cc_company2,
ccError.Id As ErrorId,
COALESCE(cic_Group,'Other Errors') As cic_Group,
COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription,
DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Where cc_company = 'IWA' And CustomerCorrection.Id In (129,1004,3228)
Order By CustomerCorrection.Id
That Returns
129 IWA 992 Other Errors Unknown Unknown 399.500000
129 IWA 1089 Other Errors Unknown Unknown 399.500000
129 IWA 1760 Other Errors Unknown Unknown 399.500000
1004 IWA 952 Other Errors Unknown Unknown 365.333333
1004 IWA 1853 Other Errors Unknown Unknown 365.333333
3228 IWA 10 Other Errors Unknown Unknown 329.375000
Here is my temp workaround:
Select a.*,b.*
From
(
Select
cc_Company, Count(Distinct CustomerCorrection.Id) as RecCount, Avg(DateDiff(hh,cc_entrydt,getdate()))/24.00 As RecAge
From
CustomerCorrection
Group By cc_Company
) As a
Inner Join
(
Select
cc_company As cc_company2,
COALESCE(cic_Group,'Other Errors') As cic_Group,
Count(Distinct ccError.Id) As ErrorRecCount,
AVG(DateDiff(hh,cc_entrydt,getdate()))/24.00 As ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Group By cc_company, COALESCE(cic_Group,'Other Errors')
) as b
On a.cc_company = b.cc_company2
Order By cc_Company,cic_group
how to create indented child rows under the parent, in SSRS report. I am quite new to SSRS, the group by function works in a different (and obvious) way then what I want.I have a sample dataset as below.
Declare @Employee Table(Code varchar(10), Name varchar(500),
Organization varchar(100), IsChild bit, ParentId varchar(10))
insert into @Employee Values('BR456','Brandon','XYZ',0,NULL)
insert into @Employee Values('CR674','Carolina','ABC',0,NULL)
insert into @Employee Values('ME230','Melissa','PQR',1,'CR674')
insert into @Employee Values('JR987','Jerome','LMN',0,NULL)
insert into @Employee Values('JO435','Joseph','IJK',0,NULL)
insert into @Employee Values('ME321','Meghan','UVW',1,'JO435')
Is it possible to make this using some formatting options?
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;
Hi All,
I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
I want to display parent/child records on report. I am not getting the proper solution.
The data is like this:
Sequence ItemCode IsParent
1 XYZ 0 'do not have child record
2 PQR 1 'have child records with sequence no 3
3 ASD 0
3 AFDGE 0
3 VDC 0
4 ASR 1 'have child records with sequence no 5
5 ASR 0
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
I think u can understand the data I need to bind, and it is like:
XYZ
+ PQR
ASD
AFDGE
VDC
ASR
On + click we can do show/hide of child records.
I m not getting how to achive this in SQL server report. Can u give some hint?
Thanks in advance
Pravin
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View 1 Replies View RelatedI 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.
View 3 Replies View Related
............child |parent|.............a1 |a | .............a2 |a |.............a11 |a1 |.............b1 |b |.............b11 |b1 |.............b111 |b1 |.............
Here is my table I want to get all childs of "a" ie {a1,a11,a2}I mean recursivelyie child,chid of child ,child of child of child ........etc up to any extentet the table containHow can i for mulate the select querry?
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
I am trying to do a Total Row with Rollup.
I am encountering an error:
Server: Msg 8120, Level 16, State 1, Line 25
Column 'CALLSTARTTIME' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Here's the query:
SELECT
CASE WHEN (GROUPING(DATEPART(yy, CALLSTARTTIME))=1) THEN 'Total'
ELSE
DATEPART(yy, CALLSTARTTIME)
END AS 'Year',
Count(*) as 'Total Calls'
FROM CALL_LOG_MASTER
GROUP BY DATEPART(yy, CALLSTARTTIME) with ROLLUP
ORDER BY DATEPART(yy, CALLSTARTTIME)
Idea outcome:
Year Total Calls
2005 100
2006 200
Total 300
Hi,I am attempting to achieve some form of report that needs to make use of sql rollup and display it as follows:Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3 SubTotal 3Events SBR/AHM NULL NULL 1 SubTotal 1 GrandTotal 4However, with my sql query, using roll up, it will look like the following which is not correct.Category Subject Matter1 Subject Matter2 Subject Matter3 No of CasesClubs Facilities Sport Facilities Swimming Pool 3Clubs Facilities Sport Facilities NULL 3Clubs Facilities NULL NULL 3Clubs Sub Total NULL NULL 3Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events SBR/AHM NULL NULL 1Events Sub Total NULL NULL 1This is the query I am using:<code>select casewhen (grouping(Cat.Description)=1) then 'Grand Total'else Cat.Descriptionend as Category,casewhen (grouping(sub.description)=1) then 'Sub Total'else Sub.descriptionend as SM1,SubSub.Description as SM2, SM.Description as SM3, count(sub.description)from tb_feedbackcase FB left join tb_category Cat on FB.Category_ID = Cat.Category_ID left join tb_subcategory Sub on FB.SubCategory_ID = Sub.SubCategory_IDleft join tb_subsubcategory SubSub on FB.SubSubCategory_ID = SubSub.SubSubCategory_IDleft join tb_SubjectMatterLV3 SM on FB.SM3_ID = SM.SM3_IDwhere fb.commenttype_id in (select commenttypes_id from tb_comment_types where description = @feedback_type)and convert(char(10),feedback_datetime,102) >= convert(char(10),@date_from, 102)and convert(char(10), feedback_datetime, 102) <= convert(char(10),@date_to, 102)group by Cat.Description, Sub.Description, SubSub.Description, SM.Description with rollup</code>How can I change it to reflect more accurately? Please help. Thanks.
View 1 Replies View Relatedl would like to get totals at the bottom of the following columns
SELECT sum(capital_Amount) AS Capital_Amount,
sum(interest_Amount) AS Interest_Amount,
sum(total_Amount) AS Total_Amount,
sum(admin_Fee) AS Admin_Fee
to sum up all totals by column. i tried using the rollup,cube like in oracle no luck. Whats the best way of achieving this? if l have to write it as a function how would l do that?
Alter View Test2
As
SELECT loan_No AS Loan_No,
date_Issued AS Date_Issued,
store AS Store,
product AS Product,
capital_Amount AS Capital_Amount,
interest_Amount AS Interest_Amount,
total_Amount AS Total_Amount,
admin_Fee AS Admin_Fee,
user_Issued AS User_Issued,
LoanBook AS Company,
status
FROM Loan
I have two tables - the first contains machine info, and the second contains monthly readings.
Table1
Serial, Model, Location, etc...
Table2
Serial, Year, Month, Reading
I would like to write a query that gives me one row for each machine that has the serial, model, and the reading for the previous 12 months. The date will be passed into the query
Query (passing 6/1/2004)
Serial, Model, ReadingFor200307, ReadingFor200308, ...., ReadingFor200406
Can anyone help me with this or tell me what topic I should be scanning the help files for?
Thanks,
Rob
Ok, I want to know if it is possible to do an order by while using the rollup in the group by. Look at the below script:
create table tmpTable
(
prod_name varchar(50) null,
prod_color varchar(50) null,
quantity int null
)
insert into tmpTable values ('table', 'blue', 12)
insert into tmpTable values ('table', 'red', 100)
insert into tmpTable values ('table', 'white', 50)
insert into tmpTable values ('chair', 'blue', 12)
insert into tmpTable values ('chair', 'red', 1)
insert into tmpTable values ('chair', 'white', 123)
insert into tmpTable values ('chair', 'yellow', 50)
SELECT CASE WHEN (GROUPING(prod_name) = 1) THEN 'Grand Total'
ELSE ISNULL(prod_name, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(prod_color) = 1) and grouping(prod_name) != 1 THEN 'Sub Total'
when grouping(prod_color) = 1 and grouping(prod_name) = 1 then ''
ELSE ISNULL(prod_color, 'UNKNOWN')
END AS Color,
SUM(quantity) AS QtySum
FROM tmpTable
GROUP BY prod_name, prod_color WITH ROLLUP
--drop table tmpTable
I want to be able to do an order by for each section of the rollup so that the quantity can be asc for both the chair part of the query and the table part.
Thanks ahead of time.
DMW
I found this great rollup query example that uses grouping and subtotals. Would it be possible to expand on this and include a groupwithin a group and subtotal each group? For example, parent product thenchild product? Help appreciated. Thanks.FrankSQL:SELECTCASEWHEN (Grouping(CategoryName)=1) THEN 'MainTotal'ELSE CategoryNameEND AS CategoryName,CASEWHEN (Grouping(ProductName)=1) THEN 'SubTotal'ELSE ProductnameEND AS ProductName,Sum(UnitPrice) as UnitPrice,Sum(UnitsinStock) as UnitsInStockFROM ProductsINNER JOIN Categories OnProducts.CategoryID = Categories.CategoryIDGROUP BY CategoryName, ProductName WITH ROLLUP*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Related
Hi,
Can anyone explain me that WHAT IS CUBE AND ROLLUP Statments for?? Why to use, Where to use and When to use???
Are they usefull in any point of view from ADO.NET???
I would like to only have 1 column rollup but I still need the other columns in the output. Here is my T-SQL code:
This is the current output:
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00
0 2007-02-14 00:00:00 test test NULL 246.00
0 2007-02-14 00:00:00 test NULL NULL 246.00
0 2007-02-14 00:00:00 NULL NULL NULL 246.00
...
...
0 NULL NULL NULL NULL 3992230.50
I would like it to look like this:
0 2007-02-14 00:00:00 test test 03-Barnes Healthcare Services 246.00
..
...
0 NULL NULL NULL NULL 3992230.50
I only want the total on the 1st column, but it rollups all of the columns.
Code Snippet
SELECT ServiceTypeCode, ServiceDate, IFirst, ILast, VendorName, Sum(ISNULL(AfterDiscountAmt,0)) AS Amount
FROM Holding_Billable
WHERE DocumentDate BETWEEN '1/1/2007' AND '12/31/2007'
AND SiteCode = 7001
GROUP BY ServiceTypeCode, ServiceDate, IFirst, ILast, VendorName, AfterDiscountAmt
WITH ROLLUP
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
So I'm making some kind of vain attempt to follow this tutorial to get summary rows, and I've followed it as best I can to the letter using my own data. But when I try to run the query, it tells me there's a syntax error near "WITHROLLUP". Am I missing something?
SELECT CASE GROUPING(Employee) WHEN 0 THEN Employee ELSE 'Total' END AS TheEmployee, CASE GROUPING(ID)
WHEN 0 THEN ID ELSE - 1 END AS TheID, SUM(WorkDays) AS TotDays
FROM EmpScheduleExceptions
WHERE (YEAR(Start) = YEAR(GETDATE()))
GROUP BY Employee, ID WITH ROLLUP
ORDER BY Employee, TotDays
I'm struggling to fin a way to use DISTINCT keyword with ROLLUP (or Cube).For example,SELECT employee_city, employee_country, COUNT(DISTINCT employee_name)FROM employeeGROUP BY employee_city, employee_country WITH ROLLUPthat query does not work.Is there a workaround?Thx.
View 2 Replies View RelatedHow do I remove repetitive value in my query with rollup result
a b c 10
a b c 10
a b null 20
x y z 10
x y z 10
x y null 20
null null null 40
I have a two tables - a machine, and a volume
Machine has id, and serial number fields
Volume has machine_id, date, and volume
I need to roll up each volume reading into quarters for the year, and return each machine and all its quarters.
So the output should look like this (only one year will be run):
MachineID Qtr1SUM(Volume) Qtr2SUM(Volume) Qtr3SUM(Volume) Qtr4SUM(Volume)
Any ideas? I was going to join the volume table four times, with each one making sure it had the appropiate dates, but that isn't working well.
Thanks,
Rob
Hai all !
What is purpose of Cube and Rollup?
Can u explain with small example?
Regards
Umapathy
Umapathy
MS has been nice enough to add the Cube and Rollup operators so I canhave totals with my results.But out of shear meanness they won't let me use them unless I use aGroup By clause in my select. I have a proc with 25 fields, and haveno desire to Group By anything- when a certain field changes, I justwant a row with the total of that field.Is this possible?Thanks,Burt
View 2 Replies View RelatedI am using the SQL GROUP BY WITH ROLLUP PER MS's
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndive/html/data01102002.asp
To create subtotals and totals.
The problem is, I want items on every line, like transaction dates, that are neither grouped by or summed. They are just for information only.
ANSI-92 SQL does not allow any column to be selected that is either not grouped on or has an aggregate function performed.
The MS example in the link above works fine, but does not present much data.
If I wanted to sum total orders and each order's subtotal for a given customer, I would also likely want the order date, who the order was placed by, etc. in each line of the report without grouping or summing that extraneous info.
Here is an example of the SQL syntax:
SELECT
CASE GROUPING (companyId) WHEN 0
THEN (SELECT CFP..Company.companyName FROM Company WHERE Company.companyId = Activity.companyId) ELSE 'TOTAL' END AS [Company name],
CASE GROUPING (dateOrder) WHEN 0
THEN (SELECT CAST(dateOrderAS VARCHAR)) ELSE 'SUB TOTAL' END AS [dateOrder],
SUM(transactionAmount) AS [Request amount]
FROM Activity
WHERE DATEPART(year, dateOrder) = DATEPART(year, '2004')
GROUP BY companyId, dateOrderWITH ROLLUP
...
But I want more details.
after I've created a new column in a table with the "Create a new column" with custom members. How do I actually remove this newly created column without recreating the dimension ?
Thank you
Tom
i want this keep query output result but don't want the "compute " this command , because this query cannot run in this dos command directc.exe , Thus, it is have any writing skill , it is possible using of rollup ?
select d.shopcode
,d.memono
,d.txdate
,d.sku
,h.depositamt
[Code] ....