Aggregate Function To Concatenate Columns Data Into A Single Row
Aug 9, 2007
Hi all,
I have a scenario which I am not able to figure out how to do it better for quite some time.
Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22
My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid
Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22
Any advise would be much appreciated.
View 6 Replies
ADVERTISEMENT
Mar 19, 2006
Hey everyone, I'm looking for a way to insert a count of a set of records into a table and then read that table and produce an XML file from that dataset. The problem that I'm coming across is that when you do this type of query you have to specify one of the values from the table and then get a count for the number of records of that particular range of values.
The table that I am trying to get the data from will look something like this:
ItemID ModuleID Description ......1 2869 blah blah blah2 2869 blah blah blah3 2869 blah blah blah4 2690 bit bit bit5 2690 bit bit bit6 2690 bit bit bit
So I count the 2869's and 2690's and place the ModuleID and the count of the ItemID's in another table.
Then I need to extract the data from the table and put it into an XML file that is readable by the ReadXML method in ASP.NET.
So far I've got the SPROC to do only one record at a time but I would like one that does the whole table rather than one at a time using a parameter.
View 5 Replies
View Related
Sep 17, 2015
How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....
View 2 Replies
View Related
May 22, 2014
Server is SQL 2000
I have a table with 10 rows with a varbinary column
I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)
example as follows
TableA
Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>
desired results in TableB
Column1 Column2
aaa <concatenated value of above binary columns>
View 9 Replies
View Related
May 21, 2015
Have this table
ACCOU NAME NAME TODATE ID EDUCAT EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
Need to get it like
ACCOU NAME NAME TODATE ID EDUCAT EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL 2018-02-11 00:00:00.000
In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.
View 11 Replies
View Related
Sep 12, 2007
Hi--
I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.
I have data coming from an Excel Spreadsheet that looks like this:
CustNumb Invoice
1 a
1 b
2 c
3 d
3 e
3 f
I would like an output that looks like this:
CustNumb Invoice
1 a, b
2 c
3 d, e, f
I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.
Thanks for any help or ideas...
Hilary
View 10 Replies
View Related
Oct 19, 2007
Can any1 tell me why i am getting an error
SELECT DISTINCT
--p.voucher,
--p.amount,
p.siteID,
b.siteID,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,
SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers
FROM
BillingTotal b,
Payment p
--WHERE
-- s.sitename=@cmb1
--AND p.siteid = s.siteid
-- p.voucher = 0
-- p.voucher = 1
GROUP BY p.siteID,b.siteID
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View 8 Replies
View Related
Mar 14, 2001
Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.
Here's what I'm trying:
select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id
but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).
I'm new to SQL and could really use the help.
Thanks
View 1 Replies
View Related
Oct 11, 2007
SSRS/SSAS. I'm using aggregate function no problem in a Table, but when I try to use same function in data field of a chart I get no results. I know that "aggregate" has only been supported since SP2, is there a known problem with using it in charts?
Thanks
Richard
View 1 Replies
View Related
Mar 3, 2006
Hi,
I am trying to concatenate single quotation mark (ie- ' ) in the select query but not succeed.
If I concatenate word India's, how should I have to write the select query..?
Its just query I have not concern with any specific database.
View 2 Replies
View Related
Feb 22, 2007
I have a query that I'm stumped on. The data has about 6000 records, of which about 460 of those have distinct dealer names and ids.
I am trying to condense this data so that there is only one record for each dealer, but my 3rd column has different values which is why some dealers have only 2 records, and others may have 10.
I know how to select distinct values, but what I want is to return a list of comma separated values for my 3rd column which consists of all the unique values from that 3rd column for that dealer.
For instance, say I have a dealer with 8 records. They have a unique ID and Dealer Name, but the 8 records appear because the dealer has 8 entries for its activity. The activities may or may not be unique.
So for example I have dealer ABC who has an id of 54, their name is ABC, but for their activites they have 8 entries (apple, orange, banana, pear, apple, banana, mango, peach).
I wnt to be able to select the distinct id, name, and distinct activities so that when my query is returned and displayed the record appears as such:
"54","ABC","apple, orange, banana, pear, mango, peach"
Does that make sense?
View 4 Replies
View Related
Mar 26, 2008
Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:
I have a sql query which brings me many records.
This records have a column in common which have the same value (COL1)
There is a second column (COL2) which has different values bewteen these records.
I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.
Let me give you an example:
COL1 COL2
RECORD1 1-A HHH
RECORD2 1-A GGG
RECORD3 1-B LLL
RECORD4 1-B MMM
RECORD4 1-B OOO
RECORD5 1-C NNN
Me result should be:
COL1 COL2
RECORD 1-A HHHGGG
RECORD 1-B LLLMMMOOO
RECORD 1-C NNN
It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS.
Thanks for any help you can give me.
View 5 Replies
View Related
Jan 13, 2005
Hi
This is probably a very basic question for most people in this group.
How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure.
Here is an example of what i want to acheive
FName
John?Doe
FName LName
John Doe
thanks for the help
prit
View 7 Replies
View Related
Jan 8, 2007
HiI want to achieve the following transformation of data using a storedprocedure.Sourcecol1 col2(varchar)-------------------------1 1.11 1.22 2.12 2.22 2.3=================Desired Resultcol1 col2(varchar)--------------------------1 1.1 | 1.22 2.1 | 2.2 | 2.3=====================Thanks in advance. :)- Parth
View 12 Replies
View Related
Oct 28, 2015
I have the following results:
CustomerProductName ValueChargesNewCharges
13AZ 40005056
13BY 30023
13BX 50003536
13BW 16001312
13BV 107009392
13BU 7000109110
And would like to get my results in 1 row with the Customer then 2 columns for Products, 6 columns for Name/Value/Charges/NewCharges.
View 9 Replies
View Related
Mar 30, 2014
I have a table like so
code remarks
1234 abcd
4567 efgh
I need to concatenate the values in the remarks column into one row like below
abcd, efgh....how is it possible
View 4 Replies
View Related
Dec 19, 2006
Hi:
I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks
MA2005
View 9 Replies
View Related
Jul 5, 2006
Hi,
In my data flow taks, The Source data is coming from AS400 has 4 columns,
I need to achieve the followings and require your help.
1. Generate a new column which will be combination of concating these 4 columns.
2. Need to add an extra row for Header & Footer.
Please Help.
View 7 Replies
View Related
Oct 19, 2015
I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.
Error: "Conversion failed when converting the varchar value ';' to data type tinyint".
select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined
from Sampling.dbo.account_test t1
where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20
View 4 Replies
View Related
Feb 21, 2008
Does anyone know how to make a query and use an aggregate function? This is my current code...any help would be great.
"SELECT tblTopic.Topic_ID, tblTopic.Subject, MAX(tblThread.Message_date) AS MessageDate, tblThread.Message FROM (tblThread INNER JOIN tblTopic ON tblThread.Topic_ID = tblTopic.Topic_ID) WHERE (tblThread.Message_Date LIKE '%' + @fldGenus + '%' GROUP BY tblTopic.Topic_ID, tblTopic.Subject, tblThread.Message">
Also, How can i limit the query to only bringing up 5 records?
I'm trying to get a datagrid to show the 5 most recent forum posts for a particular category.
Thanks.
View 2 Replies
View Related
Mar 9, 2007
Hello, I need to use last() aggregate function in MS SQL Server 2005 but it is not built in.How to replace this functionality?Thanks.
View 4 Replies
View Related
Apr 3, 2007
I have three tables, tblschedule, tblresource and tblemployeename.
in tblschedule table there are scheduleID, resourceID and employeeID. In tblResource there are ResourceID and ResourceName. In tblemployeename there are EmployeeID, EmployeeFName and EmployeeLame. I want to have a report that show how many times the resource has been reserved by employee. i would like to have a report. Look like the following:
ResourceName EmployeeFName EmployeeLName (Or use EmployeeName) Number of record.
How to write a query? I use asp.net as front end.
Thank for your help.
View 1 Replies
View Related
Mar 4, 2004
Hi
I have a query where I need to use an aggregate function MAX in where clause, I know that MAX cannot be used in a Where clause because it is an aggregate function. Can anyone help me out in writing this query?
SELECT * FROM ACCOUNT_REVIEW AR INNER JOIN
QUESTION_RESPONSE ON
AR.Review_ID = QUESTION_RESPONSE.Review_ID
WHERE (MAX(AR.Review_Date) IS NULL)
I need it asap. Thanks in advance,
K
View 6 Replies
View Related
Mar 8, 2004
I am trying to write an update statement based on an aggregate and it will not let me. Please find below the SQL.
update abtimesummary
set hours = sum(a.hours)
from abtimestore a
join abtimesummary b
on (cast(a.weekno as varchar(10)))+'-'+(cast(a.empno as varchar(10))) = b.summaryid
and this is the error message:
Server: Msg 157, Level 15, State 1, Line 2
An aggregate may not appear in the set list of an UPDATE statement.
Can someone tell me how to get round this please?
Many thanks
View 2 Replies
View Related
Jun 15, 2004
hi there,
I have a query that works on sybase and want to make it also works on SQL Server.
The problem is that in this query I 'm using a subquery in an aggregate function.
It seems that SQL Server unlike Sybase doesn't support the use of subquery in aggregate function.
How can I overcome this problem.
Thanks,
Fady
View 3 Replies
View Related
Jan 20, 2006
I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but
SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid
but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'
Is there an elegant solution for this?
Thanks,
View 1 Replies
View Related
Aug 7, 2007
Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yet
View 6 Replies
View Related
Apr 11, 2008
I have a table like this below:
<PRE>
Page Book Release MaxPages
1234ABC A1
1234ABC B2
9999ABC D1
9999ABC E2
7777ABC A1
7777ABC C2
</PRE>
I want to select every page of the book, but only the highest release of that page.
Something Like:
Select Book, Page, Max(Release), MaxPages
From Table
But I can't quiet figure it out.
Thanks for any help.
View 11 Replies
View Related
Feb 6, 2014
If you use Coalesce with Count will it return all values even when null? Reason I pose this question to you is that I am running the below query and am getting escalated results than what should be returned. 1st stop on the debug train is how does Coalesce handle it....for example Boston should return only 143 but the query retunrs 194 for Boston?
Code:
Select
Count(NumOnsite),
originatingCity,
Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames)) As PaperNames
From readytoshipOffsite
Group By originatingCity, Coalesce(Convert(varchar(4000),NewspaperNames), Convert(varchar(4000),MagazineNames))
View 11 Replies
View Related
Jul 2, 2015
Why aren't we getting proper MAX(DOJ) value in Weird_Maxgroup Column in the below query ? Similar thing happens with MIN() function as well.
Is there something that I am missing here ?
;WITH CTE AS
(
SELECT 1 AS ID, 10000 AS SALARY,GETDATE() AS DOJ
UNION ALL
SELECT 1, 13000,GETDATE() - 10
UNION ALL
SELECT 3, 190000,GETDATE() + 10
[Code] ....
View 9 Replies
View Related
May 6, 2008
Hello,
I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!
The enc_timestamp is my date field.
Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
ORDER BY a.enc_timestamp, b.first_name, b.last_name;
Thanks in advance,
Sherri
View 17 Replies
View Related
May 22, 2008
Hi guys,
Does anybody know a convenient way to return the product of a column, similar to something like the 'sum' function?
If I have a table of form id-date-value, I'd like to do something like:
select id, date, product(val)
If not, does anyone know of a way one could structure this as a subquery?
If all else fails, I suppose I'll have to resort to a loop or cursor, but I'd rather not.
Any help would be much appreciated.
View 11 Replies
View Related
Jan 15, 2014
I am trying to use the following syntax and it is saying I can't use an aggregate function in a subquery. I can't use a GROUP BY in this case because if another field in the project table (such as status) is different, that project will show up twice.So in this case I am using this syntax to show the most recent quote within the project.
SELECT PROJECT.*, QUOTE.QuoteDate, QUOTE.QuoteCode
FROM PROJECT LEFT JOIN QUOTE ON PROJECT.ProjectID = QUOTE.ProjectID
WHERE QUOTE.QuoteDate=(SELECT Max(Q.QuoteDate) FROM QUOTE Q WHERE Q.ProjectID = PROJECT.ProjectID);
My goal here is to show the most recent quote within each project (there can be multiple revisions of a quote within each project). I want to show other fields such as the status of the quote, but if the status is different between quotes, the GROUP BY on that field will cause it to be listed more than once. All I want to show is the most recent quote for each project.
View 3 Replies
View Related
