Concatenate List Of Values Under A Group
Aug 4, 2007
I have the following dataset:
State ZIP Homes Schools
WA 98007 2000 4
WA 98052 3000 5
WA 98079 2000 3
Now if I have set the group by expression on State but as display if I want to show it as €œ[98007, 98052, 98079]€? how can I accomplish this.
My report needs to show:
State Homes Schools
[98007, 98052, 98079] 7000 12
Any help will be greatly appreciated
View 6 Replies
ADVERTISEMENT
Sep 29, 2012
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 .
View 2 Replies
View Related
Jul 3, 2015
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?
View 1 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Jun 15, 2015
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
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
View 8 Replies
View Related
Mar 21, 2002
Can I avoid cursor (use while of if statement instead ) if for example I have two sales people for the same company and I need one row per company?
View 1 Replies
View Related
May 20, 2014
Give this data set;
declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),
[Code] ....
select * from @BO
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:
declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),
[Code] ....
View 7 Replies
View Related
Sep 17, 2015
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
View 2 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
Oct 14, 2015
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.
View 2 Replies
View Related
Sep 7, 2015
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.
View 2 Replies
View Related
Jul 20, 2005
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!
View 3 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Apr 16, 2004
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
THanks in advance for any suggestions!
Raul
View 2 Replies
View Related
Oct 11, 2007
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.
View 12 Replies
View Related
Jan 19, 2008
Hi,
Is there any way to group variables present in a select statement.
My code:
SELECT @var1=something, @var2=something_else
FROM ...
GROUP BY @var1
I wanted to group by 'something' hence I used a variable to assign it to. But I'm unable to group it.
Any thoughts?
Thanks,
Subha
View 1 Replies
View Related
Jun 21, 2006
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’ “
How can I do that?
Thanks.jsn
View 6 Replies
View Related
Apr 28, 2007
Dear Readers of this post,
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.
any ideas
Sat
View 3 Replies
View Related
Nov 22, 2005
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" />
View 1 Replies
View Related
May 16, 2006
While experimenting I got this message:
[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?
TIA,
Barker
View 1 Replies
View Related
Oct 31, 2006
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
View 4 Replies
View Related
Apr 20, 2006
I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.
So my query looks like:
SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????
I need the results returned in the order provided in the IN list (3,104,43,22,345).
Thanks in advance!
pr0
View 5 Replies
View Related
Sep 27, 2006
I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.
View 14 Replies
View Related
Jul 12, 2012
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
View 4 Replies
View Related
Sep 7, 2012
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31
View 3 Replies
View Related
Apr 16, 2014
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.
View 7 Replies
View Related
May 22, 2008
Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:
START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10
RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10
Thanks!
View 8 Replies
View Related
Nov 3, 2014
select pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 24420
select distinct pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 23528
It seems there are some rows with same birthdate and operationdate.
I want to get the list of rows(with all columns) that has got same birthdate and operationdate
View 1 Replies
View Related
Feb 3, 2015
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?
View 2 Replies
View Related
Mar 24, 2015
SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,
[code]....
sb.CarrierId data is Sec.
Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values.
I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. It fails now and displays sec values in resultset.
View 1 Replies
View Related
Jul 20, 2005
Can anybody help on this question?When I select 3 values from the drop down list box, how can I insert into atable?Please help
View 3 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Aug 10, 2015
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.
View 4 Replies
View Related
