SQL Server 2012 :: Obtaining A Comma Delimited List For Each Group In The Output Of A Group By Query?
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
ADVERTISEMENT
Jan 29, 2014
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...
View 5 Replies
View Related
Jul 2, 2005
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.
Here is an approach I am trying:
TABLE Events(EventID int, Categories varchar(200))
EventID Catetories
--------------------------
1 ‘6,8,9’
2 ‘2,3’
PROCEDURE ListFilteredEvents
@FilterList varchar(200) -- contains ‘3,5’
AS
SELECT EventID FROM Events
WHERE (any value in Categories) IN @FilterList
Result:
EventID
----------
2
How can I select all records where any value in the Categories column
matches a value in @FilterList. In this example, record 2 would be
selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when
selecting records where a column value is in the parameter list, but I
can’t see how to make this work when the column itself also contains a
comma delimited list.
Can someone suggest an approach?
Any examples would be greatly appreciated!
Gary
View 3 Replies
View Related
Aug 13, 2007
Is it possible to get a comma delimited list of the views in a DB?
View 7 Replies
View Related
Mar 18, 2008
How can I get a comma delimited list of the views in my db?
("view1","view2","view3",etc...)
View 3 Replies
View Related
Jul 11, 2005
Hi,
I have a complex query where each row in the final dataset is a
product.
However each product has a number of authors associated with it.
What I
would like to do is have a query/subroutine join the authors to the
product,
as a string:
ProductID
Title
Authors
1 The Sacred and the Profane John Rieggle, George
Alexi
2 Dancing
in the Dark Dan
Brown, Peter Kay, Paul
Dwebinski
Products
Table
==============
ProductID
Title
Authors
Table
=============
AuthorID
Name
Product Authors
Table
=====================
AuthorID
ProductID
Is this at all
possible?
Thanks
jr.
View 3 Replies
View Related
Jul 12, 2006
Hi,
I have the following query:
SELECT *
FROM News INNER JOIN
Newsletters ON News.ID = Newsletters.fkNewsID
My problem is that fkNewsID can contain a comma delimited list of various IDs. Is there a way to properly do the join in this case?
View 1 Replies
View Related
Aug 17, 2007
Hello any MS SQL experts out there! please help if you can. i'm trying to run a subquery
within a query to keep myself from having to loop over the original query on display and
then run additional queries to get the further info. here's the setup. i have two tables:
persons table
column: name (varchar)
column: vehicleids (varchar)
vehicles table
column: id (int pk)
column: vehiclename (varchar)
- The persons table is a list of peoples' names and what kind of vehicle/s they own.
- The persons.vehicleids field is a comma-delimited list of one or more integers which correspond to the vehicles.id field.
- The vehicles table contains a list of vehicles like car, bicycle, motorcycle, etc, distinguished by the vehicles.id field.
The result i want returned by the query is:
NAME - VEHICLES
Joe Somebody - car,bicycle
Sheila Johnson - van,pogostick,motorcycle
John Nobody - skateboard,car
The query i'm trying to run to get this result is:
Code Snippet
SELECT pe.name,
(
SELECT ve.vehiclename
FROM vehicles ve
WHERE CAST(ve.id AS VARCHAR) IN (pe.vehicleids)
) AS vehicles
FROM persons pe
ORDER BY pe.name
It returns the persons names in the first column, but only returns vehicle names in the
second column when there's a single id in the persons.vehicleids field. if there's more
than one integer in the field, it returns an empty string.
Can anyone tell me what I'm doing wrong? I do have the option of table restructuring if
its necessary, but I'm not looking for a stored procedure solution or a temp table
solution. Any takers? I would be in the kharmic debt of anyone providing a workable
avenue.
Thank you,
Tyler
View 4 Replies
View Related
Jan 20, 2006
Have 2 tables in SQL Server 05 DB:
First one is MyList
user_id -> unique value
list -> comma-delimited list of user_ids
notes -> random varchar data
Second one is MyProfile
user_id -> unique value
name
address
email
phone
I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @user_id for MyList then return all this data.
The format of the comma-delimited data is as such (all values are 10-digit alphanumerics):
d25ef46bp3,s46ji25tn9,p53fy76nc9
The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned).
Thank you!
View 5 Replies
View Related
Jan 14, 2014
Trying to run a simple query below
Update CustomerID
set CustomerLevelID='5'
where CustomerID='97000,57700,560046,462334,454453,522444,539743'
When I try this I get
Conversion failed when converting the varchar value
Is it possible to use comma delimited values in a where command?
View 3 Replies
View Related
May 2, 2008
I am trying to pass a comma delimited llist of names to a stored procedure. I am getting a syntax error and I can't seem to figure out why. When i plug the names by hand into my select statement in query analyzer it all works fine.
Just a little background so i don't have to put all the code in... the list of name is built dynamically.
Below are all the code snippets. Thanks for your help in advance.
The is the list of names and the call to the stored procedure:
Code:
employeeList = 'name1','name2',name3','name4','name5'
SQL="sp_REVIEW @ACTION='lde', @CURRENT_USER='" & currentUser & "', " &_
"@EMPLOYEE_LIST='" & employeeList & "'"
Here is the stored procedure
Code:
SELECT ww.ORACLE_USER_NAME, ww.LAST_NAME + ', ' + ww.FIRST_NAME as employeeName,
ww1.DIVISION + ' - ' + ww1.COST_CENTER + ' - ' + ww1.COST_CENTER_DESC as department
FROM WHOS_WHO.dbo.HR_PEOPLE_V ww
LEFT JOIN WHOS_WHO.dbo.HR_DEPARTMENTS_V ww1 ON ww.DEPARTMENT_ID = ww1.ORG_ID
WHERE ww.SEARCHABLE = 1 AND ww.ORACLE_USER_NAME IS NOT NULL AND ww.PERSON_TYPE = 'Employee'
AND (ww.ORACLE_USER_NAME IN (@EMPLOYEE_LIST)
OR ww.DEPARTMENT_ID IN (SELECT ud.department_id
FROM USER_DEPARTMENT ud
WHERE ud.nt_id = @CURRENT_USER))
ORDER BY ww.LAST_NAME, ww.FIRST_NAME
View 8 Replies
View Related
Jan 15, 2005
I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.
Here is how I wrote my stored procedure:
CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@abrID int,
@ddo varchar(50),
@ay varchar(50),
@strategy varchar(10),
@budgacct varchar(10),
@budgobj varchar(10),
@origamt
varchar(50),
@incrdecr varchar(50),
@review char(10),
@abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @review
WHERE ABR_ID = @abrID
UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @ddo, ABR_DETLS_AY = @ay,
ABR_DETLS_STRATEGY = @strategy, ABR_DETLS_BUDG_ACCT = @budgacct,
ABR_DETLS_BUDG_OBJ = @budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @incrdecr)
WHERE
ABR_DETLS_ID = @abrdetlsID
GO
The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.
Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?
Thanks for any help.
-D-
View 5 Replies
View Related
Jul 20, 2005
Hi, all:I have a form which lets users choose more than one value for each question.But how do I insert each value as a separate row in my table (instead ofhaving the values submitted as a comma-delimited list)?Thanks for your help.J
View 2 Replies
View Related
May 21, 2007
Hi,
I have 2 Tables
Table 1, Row 1
1. Id = 1
2. GraphPoints = 023, 045, 078 (text - data type)
I need to move data to Table 2.
Table 2 should have
1st row
1. Id = 1
2. GraphPoint = 023 (float data type)
2nd row
1. Id = 1
2. GraphPoint = 045 (float data type)
and so on
How do I do that?
Thanks.
View 3 Replies
View Related
May 10, 2007
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
ID Products
--------------------------------------------------------------------------
1 12, 45
2 10, 20
and I would like to have results as
ID Products
--------------------------------------------------------------------------
1 12
1 45
2 10
2 20
I would appreciate if someone could offer me some guidance here.
View 11 Replies
View Related
Feb 28, 2014
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
View 0 Replies
View Related
May 1, 2014
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
View 9 Replies
View Related
Jan 18, 2006
I need some help with a stored procedure to insert multiple rows into a join table from a checkboxlist on a form. The database structure has 3 tables - Products, Files, and ProductFiles(join). From a asp.net formview users are able to upload files to the server. The formview has a products checkboxlist where the user selects all products a file they are uploading applies too. I parse the selected values of the checkboxlist into a comma delimited list that is then passed with other parameters to the stored proc. If only one value is selected in the checkboxlist then the spproc executed correctly. Also, if i run sql profiler i can confirm that the that asp.net is passing the correct information to the sproc:
exec proc_Add_Product_Files @FileName = N'This is just a test.doc', @FileDescription = N'test', @FileSize = 24064, @LanguageID = NULL, @DocumentCategoryID = 1, @ComplianceID = NULL, @SubmittedBy = N'Kevin McPhail', @SubmittedDate = 'Jan 18 2006 12:00:00:000AM', @ProductID = N'10,11,8'
Here is the stored proc it is based on an article posted in another newsgroup on handling lists in a stored proc. Obviously there was something in the article i did not understand correctly or the author left something out that most people probably already know (I am fairly new to stored procs)
CREATE PROCEDURE proc_Add_Product_Files_v2/*Declare variables for the stored procedure. ProductID is a varchar because it will receive a comma,delimited list of values from the webform and then insert a rowinto productfiles for each product that the file being uploaded pertains to. */@FileName varchar(150),@FileDescription varchar(150),@FileSize int,@LanguageID int,@DocumentCategoryID int,@ComplianceID int,@SubmittedBy varchar(50),@SubmittedDate datetime,@ProductID varchar(150)
ASBEGIN
DECLARE @FileID INT
SET NOCOUNT ON
/*Insert into the files table and retrieve the primary key of the new record using @@identity*/ INSERT INTO Files (FileName, FileDescription, FileSize, LanguageID, DocumentCategoryID, ComplianceID, SubmittedBy, SubmittedDate) Values (@FileName, @FileDescription, @FileSize, @LanguageID, @DocumentCategoryID, @ComplianceID, @SubmittedBy, @SubmittedDate)
Select @FileID=@@Identity
/*Uses dynamic sql to insert the comma delimited list of productids into the productfiles table.*/ DECLARE @ProductFilesInsert varchar(2000)
SET @ProductFilesInsert = 'INSERT INTO ProductFiles (FileID, ProductID) SELECT ' + CONVERT(varchar,@FileID) + ', Product1ID FROM Products WHERE Product1ID IN (' + @ProductID + ')' exec(@ProductFilesInsert) EndGO
View 4 Replies
View Related
Nov 19, 2013
I need a query which results the output as comma separated
for example ,
select name from tablename;
say for example this produces the following,
Name
Jack
John
Smith
I need the follwoing,
Jack,John,Smith
View 2 Replies
View Related
Oct 14, 2015
I am trying to create a comma delimited list of InvNo along with the JobNo .
CREATE TABLE #ListString
(
JobNo VARCHAR(10),
InvNo VARCHAR(MAX)
)
INSERT INTO #ListString ( JobNo, InvNo )
SELECT '3079', 'abc'
[Code] ....
View 6 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
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Apr 18, 2014
I have a requirement for SSRS where the input has the following structure:
Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu
but the user would like to see the following:
Store Number
View 1 Replies
View Related
Apr 18, 2014
I have a requirement for SSRS report where part of the input has the following structure:
Store NumberStore Owner
542 Jaklin Givargidze
542 Raymond G. Givargidze
557 Hui Juan Lu
557 Tong Yu Lu
but the user would like to see the following:
Store Number Store Owner
542 Jaklin Givargidze, Raymond G. Givargidze
557 Hui Juan Lu, Tong Yu Lu
I am sure that this can be coded, just don't know how. I believe that proper term is to "serialize" the values.
View 2 Replies
View Related
Jun 15, 2015
I have to use a Group By in Sub query that will give me my desired results.
SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.
View 8 Replies
View Related
Sep 13, 2005
I have a sql statement and one of the arguments I want to pass is a comma delimited set of numbers. It keeps getting turned into a string. How do I keep that from happening. Here is kind of what it looks likeSelect FirstNamefrom Userwhere NameID in (5,6,7)or Select FirstNamefrom Userwhere NameID in (@NameIDList)There is no error code just nothing returns. If I take out the @ANameIDList and put the values I want, it returns the correct results.Thanks,Bryan PS the link to the original thread it here http://forums.asp.net/1046154/ShowPost.aspx
View 1 Replies
View Related
Nov 30, 2005
Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:
SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'
Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.
View 4 Replies
View Related
May 30, 2014
I have the some data in one of a temporary table inside a stored procedure. I want to take the first and last value for every group and the number of records for every group. For example if you run the following script you will get a set of result.
create table #sampledata
(
userId int,
BaseYear int,
TotalSales float
)
insert into #sampledata
select 1,2008,25000 union
[code]....
From that set of result I want to get the data in the following format:
userId StartValue EndValue Records
1 25000 25200 5
2 25050 26200 4
3 53504 23804 6
I am just not able to write the correct query.
View 9 Replies
View Related
Nov 18, 2014
write the T-SQL to get the top 1 row in the group.
Sample data
Stack RefID Name
DM1-102Australia Post
DM2-102Australia Post
DM6-102Australia Post
DM3-102Australia Post
DM4-102Australia Post
DM5-102Australia Post
DMASOS-102StarTrack Express
[code]...
View 6 Replies
View Related
Nov 9, 2007
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,
it was never a problem in DTS 2000.
any work around.
Thanks,
View 7 Replies
View Related
Oct 12, 2014
I would like to do a group by when the timestamp is different from an usual pattern,
create table #tbl ( V1 int, V2 datetime, V3 float)
insert into #tbl values (1,'2012-12-12 10:15', 12.5)
insert into #tbl values (1,'2012-12-12 10:35', 2.5)
insert into #tbl values (1,'2012-12-12 10:45', 1.5)
insert into #tbl values (1,'2012-12-12 11:15', 10.5)
insert into #tbl values (1,'2012-12-12 11:30', 11.5)
select sum(v3) [value], v1 from #tbl group by v1
drop table #tbl
i would like to have result set as, aggregating results of every 15 minutes and when it exceeds 15 minutes then add the same in next group by. This 15 minute interval can be specified in the query itself.
value v1
16.5 1
221
View 9 Replies
View Related
Jan 28, 2014
I have a table which I would like to format like so:
From this:
CREATE TABLE [dbo].[Table_1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[header] [varchar](50) NULL,
[citation] [varchar](200) NULL,
CONSTRAINT [PK_Table_1_1] PRIMARY KEY CLUSTERED
[Code] ....
Just as a single varchar(max) field.
View 9 Replies
View Related
Jul 30, 2014
I have 2, 2 node clusters
PROD1(cluster 1) Clustered SQL instance1
PROD2(cluster 1)
DR 1 (cluster 2) Clustered SQL insatace 2
DR 2 (cluster 2)
I have set an availability group up from the PROD instance to the DR instance.How does the AG behave if a SQL instance fails at PROD? Does it try to fail over to Node 2 on Prod before going over to DR? or bring the Replica at DR online straight away? Can we only use Manual Failover of the AG in this scenario to make use of the High Availability of the Windows cluster?
View 3 Replies
View Related