I have the following sql:  select distinct a1.Id, a1.Value from #table1 a1 where a1.Id in (select a2.Id from #table1 a2 join #table1 a3 on a2.Id = a3.Id  where a2.Value &lt;&gt; a3.Value)   It groups the same Ids from the same table, in case their respective values are different. Example output:  | Id |Value| | 1  |  1  | | 1  |  3  | | 1  |  2  |  | 2  |  4  |  | 2  |  5  |   I want to concatenate this rows into one so it looks like this:  | Id | Value |  | 1  | 1,3,2 | | 2  | 4,5   |   I tried using for xml path, but I can't figure out how to do it correctly:   select a1.Id,  SUBSTRING( ( SELECT ',' + CAST(a1.Value as varchar(50)) [text()] where a1.Id in     (select a2.Id from #table1 a2     join #table1 a3 on a2.Id= a3.Id     where a2.Value&lt;&gt; a3.Value     )  FOR XML PATH ('')  ), 2, 1000) [Value] FROM #table a1   But it is not working. I am using Microsoft SQL. I have a problem of transforming 'in sub-query' into valid sql where I could use 'FOR XML PATH('')'. I tried using cross apply, but I can't figure out how to correctly join 3 times the same table
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 :)
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
64634
I have a table that has a schema like this:  create_table "questions_tags", :id =&gt; false, :force =&gt; true do |t|         t.integer "question_id"         t.integer "tag_id"       end        add_index "questions_tags", ["question_id"], :name =&gt; "index_questions_tags_on_question_id"       add_index "questions_tags", ["tag_id"], :name =&gt; "index_questions_tags_on_tag_id"   I would like to remove records that are duplicates, i.e. they have both the same tag_id and question_id as another record.  What does the SQL look like for that?
114266
I tried to find this question somewhere else on here, and found a couple that were similar to it -- but all the answers seemed pretty convoluted, and I think it was surprising to me because I just imagined that SQL would have a way easier way to do this.  Essentially, I'm using a GROUP BY to group two values on top of each other. The coding is simple and works well, where I get these results using WITH ROLLUP at the end:  Type    Person  Count Type A  Person 1    10 Type A  Person 2    91 Type A  Person 3    13 Type A  Person 4    10 Type A  Person 5    2 Type A  NULL        126 Type B  Person 6    16 Type B  Person 7    63 Type B  Person 8    15 Type B  Person 9    22 Type B  Person 10   16 Type B  Person 11   3 Type B  Person 12   20 Type B  Person 13   44 Type B  NULL        198 NULL    NULL        360   Is there an easy way to substitute something saying if "NULL" is in the "Person" column, then make NULL AS "Subtotal" and if "NULL" is in the "Type" column, NULL AS "Grand Total?"  And if that's weirdly complicated, I'd be totally fine with just calling all of them "Total."  Thanks!
26778
How can I pivot the following SELECT statement:           SELECT sc.CID,                 sc.CodeName     as OverviewText,                 scRAG.CodeName  as RAGStatusText             FROM StatusCode      sc LEFT OUTER JOIN ProjectOverview po               ON sc.CID          = po.ProjectOverviewCID              AND po.ProjectId    = 180 LEFT OUTER JOIN StatusCode      scRAG               ON po.RAGStatusCID = scRAG.CID           WHERE sc.SCID         = 18   Such statement yields the result set that follows:  CID OverviewText    RAGStatusText --- ------------    ------------- 153 Cost            Green 154 Requirements    Yellow 155 Schedule        NULL 156 Technical       NULL 157 Testing         NULL   But, I want it to return one single row with 10 values, as shown bellow:   ----------------------------------------------------------------------------------------------  | Cost | Green | Requirements | Yellow | Schedule | NULL | Technical | NULL | Testing | NULL |  ----------------------------------------------------------------------------------------------   Can I pivot on CID?
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.
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.
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!
6749
What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list of values.  Consider the following SQL statement:  SELECT my_column FROM my_table where search_column IN (?)   Using preparedStatement.setString( 1, "'A', 'B', 'C'" ); is essentially a non-working attempt at a workaround of the reasons for using ? in the first place.    What workarounds are available?
98891
I am looking for a way in LINQ to match the follow SQL Query.  Select max(uid) as uid, Serial_Number from Table Group BY Serial_Number   Really looking for some help on this one.  The above query gets the max uid of each Serial Number because of the Group By Syntax.
91184
I have following MySQL db structure:  content ======= id | tags --------- 1  | ,abc,def,ghi, 2  | ,abc,foo, 3  | NULL 4  | ,foo,bar,   categories ========== id -- 1 2   categories_to_content ===================== id | category_id | content_id -----------------------------  1 |           1 |          1  2 |           1 |          2  3 |           2 |          3  4 |           2 |          4   What I'm trying to do is to SELECT tags assigned to category and count them. Result for given schema:  tag | category | count ---------------------- abc |        1 |     2 def |        1 |     1 ghi |        1 |     1 foo |        1 |     1 foo |        2 |     1 bar |        2 |     1   Decomposition isn't possible (I hook up into existing CMS database).  My existing query:  SELECT tags, category_id as category FROM content JOIN categories_to_content ON content_id = content.id
112987
I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning:     It is important to specify only the columns from the target table that are used for matching purposes. That is, specify columns from the target table that are compared to the corresponding column of the source table. Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying AND NOT target_table.column_x = value. Doing so may return unexpected and incorrect results.   but this is exactly what it appears I have to do to make my MERGE work.  The data I have is a standard many-to-many join table of items to categories (e.g. which items are included in which categories) like so:  CategoryId   ItemId ==========   ====== 1            1 1            2 1            3 2            1 2            3 3            5 3            6 4            5   What I need to do is to effectively replace all rows in a specific category with a new list of items. My initial attempt to do this looks like this:  MERGE INTO CategoryItem AS TARGET USING (   SELECT ItemId FROM SomeExternalDataSource WHERE CategoryId = 2 ) AS SOURCE ON SOURCE.ItemId = TARGET.ItemId AND TARGET.CategoryId = 2 WHEN NOT MATCHED BY TARGET THEN     INSERT ( CategoryId, ItemId )     VALUES ( 2, ItemId ) WHEN NOT MATCHED BY SOURCE AND TARGET.CategoryId = 2 THEN     DELETE ;   This appears to be working in my tests, but I am doing exactly what MSDN explicitly warns me not to do. This makes me concerned that I will run into unexpected problems later on, but I cannot see any other way to make my MERGE only affect rows with the specific field value (CategoryId = 2) and ignore rows from other categories.  Is there a "more correct" way to achieve this same result? And what are the "unexpected or incorrect results" that MSDN is warning me about?
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?
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.
120564
I have a table as shown below.My question is:How can I convert columns into rows?BTW,I'm using Microsoft SQL Server 2005.  my input Data    ONAY_ID  sip_RECno    KULLANICI       ONAY    1           1         user1          true    2           1         user2          true    3           1         user3          false    4           1         user4          true   Output would be   sip_RECno  user1     user2    user3     user4           1          true     true    false    true
66119
I have 3 tables and I have to inner join Table A with Table B but a left outer between Table A and Table C.   Can I combine outer and inner join in the same query? I can nest queries and attain the desired result but I am unable to do both the joins in the same query. It appears that in other SQL languages, the order of joining is important. Is this the case in SQL Server as well?    Ok, here's the scenario.  Consider 3 tables. Table A, Table F, Table D.  I will need the recordset to contain all rows in D irrespective of whether it exists in F (after it's inner joined with A). So, a outer join comes to mind. What I would need is:   First do a inner join between A and F to get a set (this may be a null set) Then do a outer join with the recordset in (1) with D