I have a very particular problem at hand.  Brief introduction: I have two columns at a database that I need to "group concatenate", in MySQL I would simply use GROUP_CONCAT to get the desired result, in SQL Server 2017 and on I would use STRING_AGG, the problem that I have is in the SQL Server 2012, which doesn't have this function.  Now, under normal circumstances I would use FOR XML PATH('') to get the solution, this is not viable since I'm running the query from the editor inside a third source application, the error that I get is      FOR XML PATH('') can't be used inside a cursor   For the sake of the argument let's assume that it's completely out of question to use this function.   I have tried using recursive CTE, however, it's not viable due to execution time, UNION ALL takes too much resources and can't execute properly (I  am using the data for reporting).  I will no post the screenshots of the data due to the sensitivity of the same, imagine just having two columns, one with an id (multiple same id's), and a column with the data that needs to be concatenated (some string). The goal is to concatenate the second columns for all of the same id's in the first columns, obviously make it distinct in the process.  Example:  Input:  col1  col2  1     a 1     b 2     a 3     c   Output:   col1  col2  1     a/b 2     a 3     c   Does anyone have a creative idea on how to do this?
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 :)
76384
I have a trigger where i am assigning ID to a cusotm field like below -   D.customfield__c = acc.Id;   The ID is coming as 00P28000000EwpjEA0  But i need only 00P28000000Ewpj 15 char. Is there is a way to use substring in apex trigger. I can't use formula field.
29504
What is the best way to replace all '&amp;lt' with &amp;lt; in a given database column? Basically perform s/&amp;lt[^;]/&amp;lt;/gi  Notes:   must work in  2000 Must be repeatable (and not end up with &amp;lt;;;;;;;;;;)
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)
87830
Why is using '*' to build a view bad ?  Suppose that you have a complex join and all fields may be used somewhere.  Then you just have to chose fields needed.  SELECT field1, field2 FROM aview WHERE ...   The view "aview" could be SELECT table1.*, table2.* ... FROM table1 INNER JOIN table2 ...  We have a problem if 2 fields have the same name in table1 and table2.  Is this only the reason why using '*' in a view is bad?  With '*', you may use the view in a different context because the information is there.  What am I missing ?  Regards
56801
I have a function like this:  DROP FUNCTION IF EXISTS user_repo // CREATE FUNCTION user_repo(user_id INT) RETURNS VARCHAR(20) BEGIN     DECLARE vote_value mediumint;     DECLARE score mediumint;      SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)       INTO vote_value, score     FROM reputations     WHERE owner_id = user_id       AND date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 WEEK));      RETURN CONCAT_WS(',', vote_value, score); END;//   It works as well. Now I want to also pass another parameter to the function to determine the time range. I mean I want to pass a string instead of  WEEK. Here is my new function:  DROP FUNCTION IF EXISTS user_repo // CREATE FUNCTION user_repo(user_id INT, range VARCHAR(10)) RETURNS VARCHAR(20) BEGIN     DECLARE vote_value mediumint;     DECLARE score mediumint;      SELECT coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)       INTO vote_value, score     FROM reputations     WHERE owner_id = user_id       AND date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 range));      RETURN CONCAT_WS(',', vote_value, score); END;//   But it throws this error:     The following query has failed:   CREATE  DEFINER=`root`@`localhost` FUNCTION `user_repo`(`user_id` INT,                 `range` VARCHAR(10) CHARSET utf8)        RETURNS VARCHAR(20) CHARSET utf8mb4        NOT DETERMINISTIC        CONTAINS SQL SQL        SECURITY DEFINER BEGIN DECLARE vote_value mediumint; DECLARE score mediumint; SELECT  coalesce(sum(r.vote_value), 0), coalesce(sum(r.score), 0)     INTO  vote_value, score     FROM  reputations     WHERE  owner_id = user_id       AND  date_time &gt; unix_timestamp(DATE_SUB(now(), INTERVAL 1 range)); RETURN CONCAT_WS(', ', vote_value, score); END      MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'range)); RETURN CONCAT_WS(',', vote_value, score); END' at line 9   Does anybody what's wrong?
34791
This is the announcement post of the Stack Exchange Data Explorer. Its answers were meant to iron out the initial glitches 6 to 8 years ago.  If you have found a bug or need support  today with any of SEDEs features, please ask a new question and tag it with   What does it do? Allows you to run arbitrary SQL queries on the data dumps, and share those queries with your friends. Technical details It is hosted on the Stack Exchange infrastructure , the database is hosted on the same SQL Server version all other sites are hosted on. You can use most of the TSQL commands, even create temporary objects (but not User Defined Functions). The controls used are jQuery based, the grid is  . All query results are cached, so sharing a link to a query will not result in the server going in to a tailspin. The query edit control is a slightly modified  control, I also am considering bespin cause the inter iframe communication is nasty complicated. It gives up nice syntax highlighting for queries. Parameters There is support for parameterized queries, to get it to work name your params like ##this##. For example have a . Query name and description A query can have a name and description, to name it lead with a comment, to describe it continue commenting, for example: -- This is my query name  -- This is my description -- I can span multiple lines  SELECT 1  Anonymous users can initially name a query, but can not change the names of any existing queries. Tutorial There's a  which helps you getting started in writing your own SQL queries. Logging in You can use your openid provider to log in, at the moment once logged in we will track the list of queries you execute (giving you basic history support) Also if there are any params named UserId they will be populated automatically if your EmailHash matches the one on the site being queried. Magic columns If you alias an id column with as [Post Link] it will automatically create a link in the result set to the parent site. Similarly if you alias an id column with as [User Link] it will display a link to the user page. select top 10 * from Posts will show you how tags are done. (Magic columns for images is planned) example (most controversial posts:  ) So what are you waiting for, check it out at  Any feedback would be greatly appreciated. I hope to have the login section working fairly soon and allow voting on the best queries and commenting. Featured Queries At the moment, admins have permission to feature interesting queries on this page , I am looking for better ways to manage the huge query list that is building up.
67515
Ok, here is my dilemma I have a database set up with about 5 tables all with the exact same data structure. The data is separated in this manner for localization purposes and to split up a total of about 4.5 million records.  A majority of the time only one table is needed and all is well. However, sometimes data is needed from 2 or more of the tables and it needs to be sorted by a user defined column. This is where I am having problems.  data columns:  id, band_name, song_name, album_name, genre   MySQL statment:  SELECT * from us_music, de_music where `genre` = 'punk'   MySQL spits out this error:  #1052 - Column 'genre' in where clause is ambiguous   Obviously, I am doing this wrong. Anyone care to shed some light on this for me?
25277
I need an aggregate function that MySQL doesn't provide.  I would like it to be in MySQL's flavor of SQL (that is, not in C).  How do I do this?  What I'm stuck on is creating an aggregate function -- the  don't seem to mention how this is done.  Examples of desired usage of a product function:  mysql&gt; select product(col) as a from `table`; +------+ | a    | +------+ |  144 | +------+ 1 row in set (0.00 sec)  mysql&gt; select col, product(col) as a from `table` group by col; +-----+------+ | col | a    | +-----+------+ |   6 |   36 | |   4 |    4 | +-----+------+ 2 rows in set (0.01 sec)
27629
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:  ╔══════════╦══════════╗ ║ PersonID ║ OptionID ║ ╠══════════╬══════════╣ ║        1 ║ A        ║ ║        1 ║ B        ║ ║        2 ║ C        ║ ║        3 ║ B        ║ ║        4 ║ A        ║ ║        4 ║ C        ║ ╚══════════╩══════════╝   I need to pivot that data so the results are:  ╔══════════╦═════════╦══════════╦══════════╗ ║ PersonID ║ OptionA ║ Option B ║ Option C ║ ╠══════════╬═════════╬══════════╬══════════╣ ║        1 ║       1 ║        1 ║          ║ ║        2 ║         ║          ║        1 ║ ║        3 ║         ║        1 ║          ║ ║        4 ║       1 ║          ║        1 ║ ╚══════════╩═════════╩══════════╩══════════╝   Any suggestions?
37183
Possible Duplicate:          I have seen  in SQL. I wonder how would you approach the opposite problem: splitting coma delimited string into rows of data:  Lets say I have tables:  userTypedTags(userID,commaSeparatedTags) 'one entry per user tags(tagID,name)   And want to insert data into table  userTag(userID,tagID) 'multiple entries per user   Inspired by  question  EDIT  Thanks for the answers, actually more then one deserves to be accepted but I can only pick one, and the  with recursions seems pretty clean to me. It works on SQL Server 2005 and above.   For earlier version of SQL Server the solution  can be used.  For working with text data type  will be helpful. Thanks again.
10807
maybe there is a simple answer to my problem:  I have a mssql table ('Person') with a field for first and one for last name. I am coding a Program, where the User should be able to search for persons.     SELECT id, firstname, lastname FROM Person WHERE firstname LIKE   @searchstring + '%' OR lastname LIKE @searchstring + '%'   doesn't work very well, because if the user types 'John W' nothing is found.  For example the person I search for is called John Williams: It should work with searchstrings like 'John W', 'Williams Jo', 'Will' or 'Joh'  Concatenating first and lastname and use %Searchstring% also doesn't work well, because if the searchstring is e.g. 'Will' it should find people who are called Will, Willibald, Williams or Willson or something like that, but not a Person called Goodwill e.g.  And the query should be as fast as possible, because it is executed at every keydown...  edit: forgot to mention that the database must not be changed, because it is from another company.  Thank you for your help.
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
48546
I've got some customer_comments split out into multiple rows due to database design, and for a report I need to combine the comments from each unique id into one row. I previously tried something working with this  trick but I can't recall it and must not have saved it. I can't seem to get it to work in this case either, only seems to work on a single row.  The data looks like this:  id  row_num  customer_code comments ----------------------------------- 1   1        Dilbert        Hard 1   2        Dilbert        Worker 2   1        Wally          Lazy   My results need to look like this:  id  customer_code comments ------------------------------ 1   Dilbert        Hard Worker 2   Wally          Lazy   So for each row_num there's really only one row of results; the comments should be combined in the order of row_num. The above linked SELECT trick works to get all the values for a specific query as one row, but I can't figure out how to make it work as part of a SELECT statement that spits all these rows out.   My query has to go through the whole table on its own and output these rows. I'm not combining them into multiple columns, one for each row, so PIVOT doesn't seem applicable.
14862
I am trying to query two tables and get results like the following:  Section    Names shoes      AccountName1, AccountName2, AccountName3 books      AccountName1   The tables are:  CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));  INSERT dbo.TableA(ID, Section, AccountId) VALUES (1 ,'shoes','A1'), (2 ,'shoes','A2'), (3 ,'shoes','A3'), (4 ,'books','A1');  CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));  INSERT dbo.TableB(AccountId, Name) VALUES ('A1','AccountName1'), ('A2','AccountName2'), ('A3','AccountNAme3');   I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing.  I have tried the below query and get the error message:     Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.   I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.  Here is the query I am currently trying to get working correctly.  SELECT section, names= STUFF((     SELECT ', ' + Name FROM TableB as b  WHERE AccountId = b.AccountId FOR XML PATH('')), 1, 1, '') FROM TableA AS a GROUP BY a.section
12844
Using MySQL, I can do something like:  SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;   My Output:  shopping fishing coding   but instead I just want 1 row, 1 col:  Expected Output:  shopping, fishing, coding   The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.  I've looked for a function on  and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets.  So does anyone here know how to do this?
45538
Let's say I have a table of employees, like this:  Name     Sex     Role  Bob      M       Developer Joe      M       QA   Now, I have a problem with duplicated rows in this table. I will fix it sometime but it's not the issue. What I currently need is something that queries such a table:  Name     Sex     Role  Bob      M       Developer Bob      M       Janitor Joe      M       QA Joe      M       CEO   And will output the following rows:  Name     Role  Bob      Developer, Janitor Joe      QA, CEO   It doesn't matter to me if the "Role" will be split to separate columns or be a single column with multiple values.  Using SQL Server 2008 if it matters.