I'm looking for a sql query that will find all values in "PartNumber below" and concat. using a comma if the material is listed multiple times with different sales orgs.  I've been racking my brain trying to figure it out.  I'm running SQL 2008 R2  Assume the following SQL table  PartNumber      Org ABC             1 DEF             2 FGH             3 ABC             2 FGH             5   My expected output would be:  PartNumber     Org ABC            1,2 DEF            2 FGH            3,5
1770
Consider a database table holding names, with three rows:  Peter Paul Mary   Is there an easy way to turn this into a single string of Peter, Paul, Mary?
71555
I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward.  I have this query:  SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1)) FROM `game` group by matchid, ordinal having dacount &gt; 1 order by dacount desc   which correctly gives me the ids of the rows I need to delete. The problem with this though is that I can't use this as a subquery on the DELETE because of the dacount with the having parameter. Is there another way to do this?  Here was my plan:  DELETE FROM game WHERE id IN (SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1)) FROM `game` GROUP BY matchid, ordinal HAVING dacount &gt; 1)
98204
During my work with databases I noticed that I write query strings and in this strings I have to put several restrictions in the where-clause from a list/array/collection. Should look like this:  select * from customer  where customer.id in (34, 26, ..., 2);   You can simplify this by reducing this to the question that you have collection of strings and want to create a comma-separated list of this strings in just one string.  My approach I have used so far is something like that:  String result = ""; boolean first = true; for(String string : collectionOfStrings) {     if(first) {         result+=string;         first=false;     } else {         result+=","+string;     } }   But this is as you can see very ugly. You cannot see what happens there on the first look, especially when the constructed strings (like every SQL query) is getting complicated.  What is your (more) elegant way?
24473
What is the string concatenation operator in Oracle SQL?    Are there any "interesting" features I should be careful of?    (This seems obvious, but I couldn't find a previous question asking it).
456
I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life).  In the original app, we used almost entirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concat function fairly frequently.  group_concat, by the way, does this: given a table of, say, employee names and projects...  SELECT empName, projID FROM project_members;   returns:  ANDY   |  A100 ANDY   |  B391 ANDY   |  X010 TOM    |  A100 TOM    |  A510   ... and here's what you get with group_concat:  SELECT      empName, group_concat(projID SEPARATOR ' / ')  FROM      project_members  GROUP BY      empName;   returns:  ANDY   |  A100 / B391 / X010 TOM    |  A100 / A510   So what I'd like to know is: Is it possible to write, say, a user-defined function in SQL Server which emulates the functionality of group_concat?   I have almost no experience using UDFs, stored procedures, or anything like that, just straight-up SQL, so please err on the side of too much explanation :)
48494
Can you get the distinct combination of 2 different fields in a database table?  if so, can you provide the SQL example.
28370
I have a table similar to this:  CREATE TABLE example (   id integer primary key,   name char(200),   parentid integer,   value integer);   I can use the parentid field to arrange data into a tree structure.  Now here's the bit I can't work out. Given a parentid, is it possible to write an SQL statement to add up all the value fields under that parentid and recurse down the branch of the tree ?  UPDATE: I'm using posgreSQL so the fancy MS-SQL features are not available to me. In any case, I'd like this to be treated as a generic SQL question.  BTW, I'm very impressed to have 6 answers within 15 minutes of asking the question! Go stack overflow!
108409
When I write a query like this...  select * from table1 t1 join table2 t2 on t1.id = t2.id   Does the SQL optimizer, not sure if that is the correct term, translate that to...  select * from table1 t1, table2 t2 where t1.id = t2.id   Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?  Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.
118129
Given database of the website "eMovies" that manage data about cinema movies.      Write in SQL query the most common genre (that have the biggest number of movies)   The tables:  Actors      ActorID      PK     Firstname      LastName  Directors      DirectorID   PK     Firstname      LastName  Genres      GenreID      PK     GenreDescr   Movies      MovieID      PK     MovieName      Year  ActorsMovies      MovieID      PK     ActorID      PK   DirectorsMovies      MovieID      PK     DirectorID   PK   GenresMovies      MovieID      PK     GenreID      PK    My attempt:   SELECT GenresDescr  FROM GenresMovies  NATURAL JOIN Genres  WHERE GenresMovies.GenresID=Genres.GenresID  GROUP BY GenreDescr  HAVING  MAX(COUNT(GenresMovies.MovieID));   I'm stuck, my code does not work.       EDIT: attampt number 2 after using @Darwin von Corax hint:     SELECT Genres.GenreDescr,COUNT(GenresMovies.GenreID) AS MCount   FROM GenresMovies,Genres   WHERE GenresMovies.GenreID = Genres.GenreID   GROUP BY GenresMovies.GenreID,Genres.GenreDescr   HAVING MCount=MAX(MCount);      But still the code does not work
102746
I need to merge records depending on first three common  columns and place the values  which are uncommon in the last column as comma separated . I have a requirement to do the following-  InputFile :   HWF CommonDomain  javatools       AdminServer ABC CommonDomain  resourcebundle  AdminServer HWF CommonDomain  stringeditor    AdminServer ABC CommonDomain  resourcebundle  HelpPortalServer_1 HWF CommonDomain  stringeditor    HelpPortalServer_1  HWF CommonDomain  javatools       HelpPortalServer_1   Expected Output :  ABC CommonDomain resourcebundle   AdminServer,HelpPortalServer_1 HWF CommonDomain javatools       AdminServer,IHelpPortalServer_1  HWF CommonDomain stringeditor   AdminServer,HelpPortalServer_1
42242
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.  Ex: "one,two,three,four" would return a new table with only one column containing the following values:  one two three four   Does this code look error prone to you guys? When I test it with   SELECT * FROM utvf_Split('one,two,three,four',',')    it just runs forever and never returns anything. This is getting really disheartening especially since there are no built in split functions on MSSQL server (WHY WHY WHY?!) and all the similar functions I've found on the web are absolute trash or simply irrelevant to what I'm trying to do.  Here is the function:  USE *myDBname* GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[utvf_SPlit] (@String VARCHAR(MAX), @delimiter CHAR)  RETURNS @SplitValues TABLE (     Asset_ID VARCHAR(MAX) NOT NULL )  AS BEGIN             DECLARE @FoundIndex INT             DECLARE @ReturnValue VARCHAR(MAX)              SET @FoundIndex = CHARINDEX(@delimiter, @String)              WHILE (@FoundIndex &lt;&gt; 0)             BEGIN                   DECLARE @NextFoundIndex INT                   SET @NextFoundIndex = CHARINDEX(@delimiter, @String, @FoundIndex+1)                   SET @ReturnValue = SUBSTRING(@String, @FoundIndex,@NextFoundIndex-@FoundIndex)                   SET @FoundIndex = CHARINDEX(@delimiter, @String)                   INSERT @SplitValues (Asset_ID) VALUES (@ReturnValue)             END              RETURN END
20640
I'd like to remove from a given column ($2 in the example) the duplicate fields (comma separated), but only if the ID column ($1 in the example) is the same.  Input:  A    1,2,3,4 A    8,9,10,11 A    2,3,4,11,12 B    4,5,6,7 B    6,8,9,10   Expected output:   A    1,2,3,4 A    8,9,10,11 A    12 B    4,5,6,7 B    8,9,10
67901
I have the following data:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  1   |     ...       |     ...      | MAIN ST           |  2   |     ...       |     ...      |         MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | BERRY LN          |  5   |     ...       |     ...      | BERRY LN          |  6   |     ...       |     ...      | JOHN RD           |  7   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   I want all rows with duplicates in the road column removed. It does not matter to me which duplicates are removed. The end result can look something like this:        road        |  ID  | other column  | other column |  ------------------+------+---------------+--------------+           MAIN ST           |  3   |     ...       |     ...      | BERRY LN          |  4   |     ...       |     ...      | JOHN RD           |  8   |     ...       |     ...      |   where the id's are any value, as long as the road column is unique.   Is there a simple way to do this in SQL or T/SQL? I followed the guide , but it only applies to duplicate keys with non-unique rows.
7766
This is a very basic query I can't figure out....  Let's say I have a two column table like this:  userid  |  roleid --------|--------    1    |    1    1    |    2    1    |    3    2    |    1   I want to get all distinct userids that have roleids 1, 2 AND 3.  Using the above example, the only result I want returned is userid 1.  How do I do this?
4952
I have a table that has path1, path2, and sha1 value.  For any values of path2 and sha1, there can be multiple values of path1.  I just want one of those paths.  I don't really care which one.  I'm thinking I can do a group by for path2 and sha1.  Now I just need to select one of the values of path1.  I suppose I could select the minimum value of path1 but that would be doing extra work that isn't really needed.  Google tells me that Microsoft has "FIRST" but I don't see that in the postgres pages.  Plus... I'd like to stick with normal SQL if possible.
96092
I have three columns as shown below. What is the best way to pull all the values from column 3 that are not the same in the other columns? the result should return 2, 7 and 9 as they are only in column 3.   Column 1 |  Column 2  | Column 3 ---------|------------|--------- 1        |    5       |     1 3        |    4       |     2 1        |    5       |     7 4        |    6       |     9   I tried  not in (Column 1, Column 2) method but it checks by row only and I need it to take first value in column 3 and compare against the whole column in 1 and 2....
113863
Working in Marketing cloud where we are building a query that splits apart data from a single column into 4 separate columns where the delimiter is a semicolon.   Error is displayed when doing a "check syntax"   Error message: ';' is a reserved word and may not appear in your query.   We cant change the data to use a different delimiter so wanted to know if anyone knows of a work around?   select  c.contactid as subscriber_key  , coalesce(i.Preferred_Correspondence_Name, i.[first name]) as Preferred_Correspondence_Name  , i.[email address] as email  , c.casenumber  , c.Id as case_salesforce_id  ,party.name as party  , c.LastModifiedDate as last_modified_date  , c.CreatedDate as created_date  , c.Bank_Account_Change__c as bank_account_change  ,'add' as action  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),4)) as Party_Details  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),3)) as Party_ID_Details  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),2)) as Bank_Account_Details  ,LTrim(PARSENAME(REPLACE([c.Bank_Account_Change__c],';','.'),1)) as Start_Date_Details  from  ENT.Case_salesforce_2 as c  inner join ENT.RecordType_Salesforce as rt on c.RecordTypeId = rt.Id  inner join ENT.account_salesforce as party on party.id = c.accountid  inner join contact i on i.[subscriber key]= c.contactid  where  Type = 'Payments'  and Sub_Type__c = 'Bank Account Change'  and Raised_By__c = 'External'  and Origin = 'Web'  and rt.Name = 'Financial Enquiries'  and c.Bank_Account_Change__c is not null