I want to merge rows into single column using FOR XML PATH in SQL.  Here is my Query:  select d.Device from tbl_Sales_OrderItems tsoi left join tbl_devices d  on d.DeviceID=tsoi.DeviceID where salesorderid=102  and tsoi.Quantity&gt;0 and tsoi.TypeID=1  union all  select d.Partnumber Device from tbl_Sales_OrderItems tsoi left join tbl_VendorParts d  on d.VendorPartID=tsoi.RefID where salesorderid=102  and tsoi.Quantity&gt;0 and tsoi.TypeID=2   Here i am getting two rows i.e Two Device name in from tbl_Sales_Order.   Now these rows i want to get in single row using FOR XML PATH into a single column.   Further after applying FOR XML PATH i want to use it in select query i want to use the row value which would be as follows.  Select salesorderid,@resultinRow from tbl_Sales_Orders   Output Required:  SalesOrderID  Devices   102          Device1,Device2   103          Device3,Device2
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 :)
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
60682
I have the following QUERY  =query(XX!A2:A;" select A where A&gt;'50' ")   and  =query(YY!A2:A;" select A where A&gt;'50' ")   how do I combine these two data sources so I don't have to have two separate columns they process data to?   best would be if the 1st row from XX is followed by the 1st row of YY, 2nd row followed by 2nd row... and so on If the above option is not easily possible, it is OK for one query to be followed by the second one (if I expect correctly that the start of the second will be moved with newly added data into the first one)
76446
I would like to merge multiple List into one but I am not sure on how to do it .. My code is the following: public with sharing class ApprovalDashboard {     public ApprovalDashboard(){              }          @AuraEnabled(cacheable=true)     public static List&lt;Object&gt; approvalTableData(){         List&lt;Object&gt; FinalList = new List&lt;Object&gt;();         List&lt;AggregateResult&gt; OppList = [SELECT Opportunity__r.Id  FROM Approval__c                                       WHERE (Type__c = 'Investment_Committee' OR Type__c = 'Pre_IC')                                      AND Status__c != Null                                      AND CALENDAR_YEAR(Date__c) = 2021                                      GROUP BY Opportunity__r.Id];                  for(AggregateResult item: OppList){             List&lt;Opportunity&gt; OppData = [SELECT Id, Name, StageName                                          FROM Opportunity                                          WHERE Id = :item.Id];                          List&lt;Approval__c&gt;ApprovalList = [SELECT Name, date__c FROM Approval__c WHERE Opportunity__r.id = :item.Id];             system.debug(OppData);         }                                             return OppList;              }  I would like to mege OppData and ApprovalList into one big List in the format [{}, {},{}] .. Any help ?
57120
I'm trying to create a table which looks like    but I'm not really sure how to incorporate multiple spanning rows and columns together (to add 'Organism 1' and 'Organism 2'). I've gotten to this point so far:   \documentclass[a4paper,10pt]{article} \usepackage[utf8x]{inputenc} \usepackage{amsmath}  \begin{document} \begin{tabular}{r|c|c|} \multicolumn{1}{r}{}  &  \multicolumn{1}{c}{$S$}  & \multicolumn{1}{c}{$T$} \\cline{2-3} $S$ & $a,a$ & $b,c$ \\cline{2-3} $T$ & $c,b$ & $d,d$ \\cline{2-3} \end{tabular}  \end{document}    And it currently looks like this:    Can anyone suggest how to add the top 'Organism 2' and left 'Organism 1' row/column entries?  thanks!
48977
I have a table with several columns which I want to SELECT:  SELECT his_name , her_name, other_name FROM foo;   Bu, I instead I want to combine the results all into a single column. As an example, I can do this with UNION ALL as   SELECT her_name AS name FROM foo UNION ALL SELECT his_name AS name FROM foo UNION ALL SELECT other_name AS name FROM foo   Is there more elegant way to do this operation?
98617
I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.  How things start:  SELECT COUNT(fullpath) FROM data;  12395   I use the column fullpath a lot so I create an index:  CREATE INDEX "index" ON data (fullpath);   I then add the column:  ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);   Insert values into the Sub column based on the fullpath:  insert into data(Sub) select case     when fullpath like '%subA%' then 'subA'     when fullpath like '%subB%' then 'subB'     else 'neither' end from data;   Check the number of rows again:  SELECT COUNT(fullpath) FROM data;  24790   I don't understand the syntax and the operations. Do I need to join or something to respect the index?
58715
Given a hierarchical table like this:  CREATE TABLE [dbo].[btree] (   id INT PRIMARY KEY , parent_id INT REFERENCES [dbo].[btree] ([id]) , name NVARCHAR(20) );   I would like to obtain the whole tree structure.  For instance, using this data:  INSERT INTO [btree] VALUES (1, null, '1 Root'); INSERT INTO [btree] VALUES (2,    1, '1.1 Group'); INSERT INTO [btree] VALUES (3,    1, '1.2 Group'); INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group'); INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group'); INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group'); INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group'); INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items'); INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items'); INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items'); INSERT INTO [btree] VALUES (11,   5, '1.1.1.2 Items'); INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items'); INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items'); INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');   I would like to obtain:  +----+-----------+---------------------+ | id | parent_id | description         | +----+-----------+---------------------+ |  1 |    NULL   | 1 Root              | |  2 |     1     |   1.1 Group         | |  4 |     2     |     1.1.1 Group     | |  8 |     4     |       1.1.1.1 Items | |  9 |     4     |       1.1.1.2 Items | |  5 |     2     |     1.1.2 Group     | | 10 |     5     |       1.1.2.1 Items | | 11 |     5     |       1.1.2.2 Items | |  3 |     1     |   1.2 Group         | |  6 |     3     |     1.2.1 Group     | | 12 |     6     |       1.2.1.1 Items | | 13 |     6     |       1.2.1.2 Items | |  7 |     3     |     1.2.2 Group     | | 14 |     7     |       1.2.2.1 Items | +----+-----------+---------------------+   I'm fetching records using a recursive query like this:  ;WITH tree AS (     SELECT c1.id, c1.parent_id, c1.name, [level] = 1     FROM dbo.[btree] c1     WHERE c1.parent_id IS NULL     UNION ALL     SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1     FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id ) SELECT tree.level, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description FROM tree OPTION (MAXRECURSION 0) ;   And this is the current result:  +----+-----------+---------------------+ | id | parent_id | description         | |  1 |    NULL   | 1 Root              | |  2 |     1     |   1.1 Group         | |  3 |     1     |   1.2 Group         | |  6 |     3     |     1.2.1 Group     | |  7 |     3     |     1.2.2 Group     | | 14 |     7     |       1.2.2.1 Items | | 12 |     6     |       1.2.1.1 Items | | 13 |     6     |       1.2.1.2 Items | |  4 |     2     |     1.1.1 Group     | |  5 |     2     |     1.1.2 Group     | | 10 |     5     |       1.1.2.1 Items | | 11 |     5     |       1.1.1.2 Items | |  8 |     4     |       1.1.1.1 Items | |  9 |     4     |       1.1.1.2 Items | +----+-----------+---------------------+   I can't figure out how to order it by levels.   Is there a way to set a rank for each sub-level?  I've set up a
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?
76106
I have 3 csv files like this.  csv 1:  1,aaaa,bbb,2014-04-01 2,qwe,rty,2014-04-03 3,zxc,cvb,2014-04-05   csv 2:   2,j,k,2014-04-01 3,a,s,2014-04-04 5,g,h,2014-04-08   csv 3:  2,a,s,d,f,g,2014-04-01 3,d,f,g,h,j,2014-04-06 4,c,v,b,n,m,2014-04-09   How can I merge all by the first column?  SELECT * FROM csv1 JOIN csv2 where csv1[0]= csv2[0] --[0] is the position of the first column   The output should be:   csv1 fields     | csv2 fields |  csv4 fields   2,qwe,rty,2014-04-03,a,s,2014-04-04,a,s,d,f,g,2014-04-01  3,zxc,cvb,2014-04-05,g,h,2014-04-08,d,f,g,h,j,2014-04-06
115910
When I view the result of a SELECT with MySQL Workbench, it's correct with one single \  max@host 10:13:58: ~$ mysql -h db-master.domain.local -uuser -ppw db -e ' &gt;                 SELECT &gt;                 DISTINCT i.filesourceregexp &gt;                 FROM db.ImportLogFiles i'  +------------------------------------------------+ | filesourceregexp                               | +------------------------------------------------+ | ^[0-9]{8}_1062355673_merge_google_pbn\.csv$    | | ^[0-9]{8}_8026062435_merge_google_pbn\.csv$    | | ^[0-9]{8}_1062355673_store_visits_report\.csv$ | +------------------------------------------------+  max@host 10:14:10: ~$ mysql -h db-master.domain.local -uuser -ppw db -e '                 SELECT                 DISTINCT i.filesourceregexp                 FROM db.ImportLogFiles i' | tail -n +2 ^[0-9]{8}_1062355673_merge_google_pbn\\.csv$ ^[0-9]{8}_8026062435_merge_google_pbn\\.csv$ ^[0-9]{8}_1062355673_store_visits_report\\.csv$ max@host 10:14:19: ~$    I have these options in my.cnf:  [client]  host = db-master  user = user  password = pass  default-character-set=utf8   Why does piping the result through tail change the output/string? (note the double \).
3484
I have data that looks like this:  Id | Loc1 | Loc2 | Loc3 | Loc4 ---+------+------+------+----- 1  | NY   | CA   | TX   | IL 2  | WA   | OR   | NH   | RI   And I want to convert it to this:  Id | LocNum | Loc ---+--------+---- 1  |   1    | NY 1  |   2    | CA 1  |   3    | TX 1  |   4    | IL 2  |   1    | WA 2  |   2    | OR 2  |   3    | NH 2  |   4    | RI   What's the easiest way to do this in Excel 2007?
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.
84648
I've read many posts about using pivot to get the data in the format I want, but the more I read the more confused I get.  I have this data:    That I'm trying to get into a format similar to this:   For the most part, everything I try results in an SQL error, and the only successful attempt I've had didn't return the data in the format I'm looking for.  Any help would be appreciated.
32670
Here is a simple table.  I want to merge 4 cells. I want to combine the three "this" cells area in this table. I thought I have to use multirow and multicolum at same time. However, I could not find how to use it.  If possible, could you post the modified LaTeX source code?     My LaTeX code:  \documentclass{article} \usepackage{multirow}% http://ctan.org/pkg/multirow \usepackage{hhline}% http://ctan.org/pkg/hhline \begin{document}  \begin{table}[ph] \begin{center} \begin{tabular}{c|c|c|c|c} \hline \multirow{2}{*}{Raaa (k)} &amp; \multicolumn{4}{c}{C ()} \\ \hhline{~----}  &amp; 3.3 &amp; 2.5 &amp; 1 &amp; 0.5 \\ \hline \multirow{2}{*}{Raaa (k)} &amp; \multirow{2}{*}{this} &amp; this &amp; 0.5 &amp; 0.6\\ \hhline{~~~--}            &amp;                       &amp; this &amp; 0.7 &amp; 1.2 \\ \hline \end{tabular} \caption{R, C ripple size} \label{T:peak} \end{center} \end{table} \end{document}
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!
91839
I am having trouble constructing a single XPath statement to return two different sets of attributes.    For example take the following XML document:  &lt;root&gt;  &lt;line name="one" alpha="a1" beta="b1"/&gt;  &lt;line name="two" alpha="a2" beta="b2"/&gt;  &lt;line name="three" alpha="a3" beta="b3"/&gt; &lt;/root&gt;   If I use the following XPath statement:  //@alpha   It yields the following attribute set:  alpha="a1" alpha="a2" alpha="a3"   What statement do I use to yield the following attribute set:  alpha="a1" alpha="a2" alpha="a3" beta="b1" beta="b2" beta="b3"