Is there a way to concatenate the values of a particular field together into one field for all the records in each group of a GROUP BY without using XML?
Ideally, I want to accomplish this in a stored procedure. Normally, I would handle this in the front end, but my front end is Crystal ...
For example, if my data was: 1 "A" 1 "B" 1 "C" 2 "D" 2 "E"
I want to group by the first value and concatenate the second value: 1 "A,B,C" 2 "D,E"
Very easy using code - I simply add a function to the query in Access (not very efficient, but effective), or using recordsets in the app (which is actually very efficient) except my front end is Crystal in this case .
I've got a fairly standard query that does a group by a type column, and then sums the lengths of a VARCHAR column. I'd like to add into that a concatenated version of the string always concatenating in primary key order. Is that possible?
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:
I want to concatenate the column_names with the respective values.
Create table #test1 (Id int,Name varchar(10),Country varchar(10))
insert into #test1 values ( 1,'JOHN','USA'), (2,'SAM','CANADA'), (3,'HO','CHINA'), (4,'RAM','INDIA')
select * from #test1
I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:
ID values 1,NAME-john-COUNTRY-USA 2,NAME-SAM-COUNTRY-CANADA 3,NAME-HO-COUNTRY-CHINA 4,NAME-RAM-COUNTRY-INDIA
Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic
select ID, (a+'-'+B) as Value from ( select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1
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.
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red  - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red  - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
          red   blue  green   pen       2    0    1   marker    0    1    0   pencil      1    3    0   highlighter  1    1    1Â
We tried Sum but not able to display in single row.
Hello,So my table contains say 100,000 records, and I need to group thecategories in fld1 by the highest count of subcategories. Say fld1contains categories A, B, C, D, E.All of these categories contain subcategories AA, AB, AC, AD,...AJ, BA,BB...BJ, CA, CB, CC...CJ, etc in fld2.I am counting how many subcategories are listed for each category. LikeA may contain 5 of AA, 7 of AB, 3 of AC, 11 of AD...1 for the rest and20 of AJ. B may contain 2 of BA, 11 of BB, 7 of BC, and 1 for the rest.I want to pick up the top 3 subcategory counts for each category. Wouldlook like this:Cat SubCat CountA AJ 20A AD 11A AB 7B BB 11B BC 7B BA 2So event though each category contains 10 subcategories, I only want tolist the top 3 categories with the highest counts as above. If I justdo a group by and sort I can get this:Cat SubCat CountA ... ...AAAAAA...B ... ...BBBBB...But I just want the top 3 of each category. The only way I can think ofto do this is to query each category individually and Select Top 3, andthen Union these guys into one query. The problem is that I have tohardcode each category in the Union query. There may be new categoristhat I miss. Is there a way to achieve what I want without using Union?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I need to export a list of name and email address fields from a SQLtable, in some format so that a user can take the file I generate andImport it into Outlook and it will keep all the names together in aGroup or Distribution List rather than dumping them into the user'smain address book. Any ideas?Francesco
Hard to write a subject line to describe this one.
Anayway, I have a table with names and address plus an extra field noting a part number of product.
I'd like to build a SELECT string that will return one result for each name/address (uniques only in other words) and build a comma delimited field of all the part numbers for that name/address.
Example:
NAME ADDRESS PART NUMBER John Smith 555 Main st., los angeles, ca 90003 5000 John Smith 555 Main st., los angeles, ca 90003 6650 Mike Jones 8569 West 18th Ave., San Diego, ca 1255 John Smith 555 Main st., los angeles, ca 90003 5144 Mike Jones 8569 West 18th Ave., San Diego, ca 2399
So I'd like my results to look like this:
NAME ADDRESS PARTS John Smith 555 Main st., los angeles, ca 90003 5000,6650,5144 Mike Jones 8569 West 18th Ave., San Diego, ca 1255,2399
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice :
Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Below is the my sql statement:
SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item,
ItemRef =
Case
when RH.Quantity < 0 then Sum(RH.Quantity)
when RH.Quantity >= 0 then Sum(0)
end
FROM dbo.RHISTORY AS RH INNER JOIN dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID
WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007') AND (RH.TransactionCode IN (13, 14, 15, 16))
Group by dbo.DateOnly(TH.TransactionDate), RH.Item
The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day.
Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example: selectcmd = "Select * from mytable where myfile =" ??????? cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values: A B C D E
I would like to build a query like that: selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
I have a dropdownlist that is populated with an sqldatasource as follows:
SELECT [Project_ID], [Title] FROM [Projects] WHERE [Username] = @Username AND Hide ='false'
The Datavalue vield of the DDL is populated with the [Title].
When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table.
For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?Would this work? <asp:controlParameter Name="InvoiceNumber" Type="String" ControlID="ddAdSize.Value" />
[Flat File Source [212]] Error: The "output column "Column 0" (237)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 0" (237)" specifies failure on error. An error occurred on the specified object of the specified component"
I note under BIDS, Properties tab, that the Flat File is assigned an ID of 212. But is the number (237) and ID or an error value? How can I list all "ID's" used in a package?
Hello I have a newbie question. If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag -------------------------------- 1 2012-12-12 rowA 1 2012-12-12 rowB
I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.
I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.
We're using MS Access 2010 as a frontend to an SQL server back-end. From Access, I can run read-only queries and pass-through queries. I'd like to use a local Access table as part of a join to server data. As a non-pass-through query, it's slow; about 5 min to join to 2 other tables.
I could use VBA to turn the local table into part of a pass-through query, with a large in() statement, or several where x='' ors but the local table may have 50000 entries in it. Is there a good or right way to pass this data in the query if I don't have write access to the server?
Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing
I have [TableAccount.AccountType] field which can store these values:
Value C S E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
