For simplicity, we have the following tables   Teachers  Students   Let's assume that each teacher has many students (and not the other way around, let's assume that each student has only one teacher).   How do I display the data such that:   Col 1 - Teachers - Students Row 1 - Teacher A - Student A, Student B, Student C Row 2 - Teacher B - Student X, Student Y, Student Z
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 :)
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?
10446
How do you return 1 value per row of the max of several columns:  TableName  [Number, Date1, Date2, Date3, Cost]   I need to return something like this:  [Number, Most_Recent_Date, Cost]   Query?
88918
Example, this is the data in Excel:  1 2 2 2 3 4 3 4 3 3 2 2 2 2 1 2  The rows which have consecutive duplicate column-pair values (in bold font) should be highlighted:  1 2 2 2 3 4 3 4 3 3 2 2 2 2 1 2
116167
I'm trying to set up a table with some equations in it, however I would like the equations on multiple rows to be aligned properly. So far I have  \begin{tabular}{cc} \hline \multicolumn{1}{l}{Header 1} \\ \hline $a = \sum_j b(c)$ &amp; $c = d$ \\ \hline \multicolumn{1}{l}{Header 2} \\ \hline $\begin{aligned}          e&amp;=fghij(y) \\          klm(x)&amp;=nop         \end{aligned}$ &amp;        $\begin{aligned}         qr&amp;=vwxyzstu \\         abcde&amp;=fghij         \end{aligned}$ \end{tabular}   which looks like    Note how the equals signs in the top row are not aligned with the corresponding equals signs in the bottom two rows.   So my question is; is there a way to align across different rows or am I going about this completely the wrong way?
79492
I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of first three. so for all the combinations of three columns there is different value for the fourth column. Now I want a design for data like this. Any help is appreciated.
81303
I have a code that prints a table with 2 rows. I want to keep format of it and change 2 rows into 3.   \documentclass[conference]{IEEEtran}  \usepackage{pgfplots} \usepackage{siunitx} \usepackage{multirow} \begin{document}  \begin{table}[h] \caption{Family data set results} \label{tbl::Fam} \centering \begin{tabular}{|l|l|c|c|c!{\vrule width 2pt}l|l|c|c|c|} %\noalign{\hrule height 2pt} \hline \textbf{Column}&amp;\textbf{Algorithm}&amp;\textbf{min}&amp;\textbf{max}&amp;\textbf{std}l&amp;\textbf{Column}&amp;\textbf{Algorithm}&amp;\textbf{min}&amp;\textbf{max}&amp;\textbf{std}\\\hline \multirow{2}{*}{age}&amp;Original&amp;9&amp;2.8&amp;100&amp;\multirow{2}{*}{employer\_type}&amp;Original&amp;13&amp;2&amp;10\\ \cline{2-5} \cline{7-10}&amp;PrivBayes&amp;2&amp;95&amp;96&amp;&amp;PrivBayes&amp;2&amp;100&amp;100\\\hline \multirow{2}{*}{education\_level}&amp;Original&amp;7&amp;2&amp;98&amp;\multirow{2}{*}{marital\_status}&amp;Original&amp;10&amp;2&amp;100\\ \cline{2-5} \cline{7-10}&amp;PrivBayes&amp;2&amp;6&amp;96&amp;&amp;PrivBayes&amp;2&amp;6&amp;100\\\hline \hline %\noalign{\hrule height 2pt} \end{tabular} \end{table}  \end{document}   In this picture, I have 2 algorithms {Original, PrivBayes}. I want to change 2 algorithms into 3 algorithms with keeping the format of table. Can you please help me to do it?
34846
I have a TEMPORARY table containing pairs of key/value as  id   col1      col2        type         pair 1    key1      key2        key          1 2    value1    value2      value        1 3    key3      key4        key          2 4    value3    value4      value        2 5    key5      key6        key          3 6    value5    value6      value        3   I want to mix the key/values as  pair    key_col1     key_col2      value_col1      value_col2 1       key1         key2          value1          value2 2       key3         key4          value3          value4 3       key5         key6          value5          value6   Since, it is a temporary table, I cannot use JOIN. How can I SELECT from this table to have pairs of key/value in each row?
37524
Given Table 1 and Table 2, I want to create a Table 3 whose columns are     Column1(Table3) = max{Column1(Table1), Column1(Table2)}      Column2(Table3) = max{Column2(Table1), Column2(Table2)}      Column3(Table3) = max{Column3(Table1), Column3(Table2)}      Column4(Table3) = max{Column4(Table1), Column4(Table2)}   So, if I copy the elements all from table 1 or all from table 2 to table 3, the layout of table 3 will not change. (The Table 3 below just shows the column width. You can ignore the elements in it.)  \documentclass{article}   \begin{document}     \begin{table} \centering \begin{tabular}{|l|l|l|l|}     \hline     Column1 &amp; Column2 &amp; Column3 &amp; Column4 \\ \hline     The &amp; quick brown &amp; fox &amp; jumps over the lazy dog \\      The &amp; quick brown &amp; fox &amp; jumps over the lazy dog \\     The &amp; quick brown &amp; fox &amp; jumps over the lazy dog\\     \hline \end{tabular} \caption{First table} \end{table}   \begin{table} \centering \begin{tabular}{|l|l|l|l|}       \hline     Column1 &amp; Column2 &amp; Column3 &amp; Column4 \\ \hline     The  quick &amp; brown &amp; fox  jumps &amp; over the lazy dog \\     The  quick &amp; brown &amp; fox  jumps &amp; over the lazy dog \\     The  quick &amp; brown &amp; fox  jumps &amp; over the lazy dog\\     \hline \end{tabular} \caption{Second Table} \end{table}   \begin{table} \centering \begin{tabular}{|l|l|l|l|}       \hline     Column1 &amp; Column2 &amp; Column3 &amp; Column4 \\ \hline     The  quick &amp; quick brown &amp; fox  jumps &amp; jumps over the lazy dog \\      The  quick &amp; quick brown &amp; fox  jumps &amp; jumps over the lazy dog \\     The  quick &amp; quick brown &amp; fox  jumps &amp; jumps over the lazy dog\\     \hline \end{tabular} \caption{Desired Width} \end{table}   \end{document}
86051
This question is similar, yet different, to another DBA SE question I have here:     I have a FINANCE_SOURCE table:  +-----------+------------+-------------+--------+------+ | SOURCE_ID | PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR | +-----------+------------+-------------+--------+------+ |       101 |          1 | A           |  10.00 | 2017 | |       102 |          1 | B           |   5.00 | 2017 | |       103 |          1 | B           |  15.00 | 2017 | |       104 |          1 | B           |  70.00 | 2016 | |       105 |          1 | C           |  30.00 | 2017 | |       106 |          1 | D           |   1.00 | 2016 | |       107 |          1 | D           |  20.00 | 2017 | +-----------+------------+-------------+--------+------+   I want to rearrange the data, so that SOURCE_A, SOURCE_B, and OTHER(anything that's not A or B) are in separate columns.  The data needs to be grouped into separate YEARS:  +------------+---------+---------+-------------+-------+------+ | PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR | +------------+---------+---------+-------------+-------+------+ |          1 |      10 |      20 |          50 |    80 | 2017 | |          1 |         |      70 |           1 |    71 | 2016 | +------------+---------+---------+-------------+-------+------+   How can I do this? Performance is important.
108130
I got the following table on google sheets.   Text version +----+---+---+----+ |    | A | B | C  | +----+---+---+----+ | 1  | 1 | 2 | 1  | +----+---+---+----+ | 2  | 2 | 1 | 1  | +----+---+---+----+ | 3  | 3 | 1 | 4  | +----+---+---+----+ | 4  | 3 | 2 | 7  | +----+---+---+----+ | 5  | 3 | 3 | 8  | +----+---+---+----+ | 6  | 3 | 4 | 1  | +----+---+---+----+ | 7  | 4 | 1 | 3  | +----+---+---+----+ | 8  | 4 | 2 | 4  | +----+---+---+----+ | 9  | 4 | 3 | 13 | +----+---+---+----+ | 10 | 4 | 4 | 1  | +----+---+---+----+ | 11 | 4 | 5 | 3  | +----+---+---+----+ | 12 | 5 | 3 | 5  | +----+---+---+----+ | 13 | 5 | 4 | 14 | +----+---+---+----+ | 14 | 5 | 5 | 5  | +----+---+---+----+ | 15 | 5 | 3 | 8  | +----+---+---+----+ | 16 | 6 | 4 | 27 | +----+---+---+----+ | 17 | 6 | 5 | 16 | +----+---+---+----+ | 18 | 5 | 5 | 1  | +----+---+---+----+ | 19 | 7 | 4 | 4  | +----+---+---+----+ | 20 | 7 | 5 | 1  | +----+---+---+----+ | 21 | 8 | 3 | 2  | +----+---+---+----+ | 22 | 8 | 4 | 72 | +----+---+---+----+ | 23 | 8 | 5 | 55 | +----+---+---+----+   I want the first column to represent the groups in x axis. The 2nd column is supposed to be the a group in a certain x value lets say I have 8 3 22 8 4 55 8 5 72 I want to have at value x = 8 , 3 bars, the first bar has the y value of 22 with the label 3, the 2nd has y value of 22 with label 4, and the 3rd has y value of 72 with label 5. This is the chart I got which is completely not the one I wanted.
108733
I have a table that looks like this:  RECIPE     VERSION_ID     INGREDIENT    PERCENTAGE 4000       100            Ing_1          23,0 4000       100            Ing_100         0,1 4000       200            Ing_1          20,0 4000       200            Ing_100         0,7 4000       300            Ing_1          22,3 4000       300            Ing_100         0,9 4001       900            Ing_1           8,3 4001       900            Ing_100        72,4 4001       901            Ing_1           9,3 4001       901            Ing_100        70,5 5012       871            Ing_1          45,1 5012       871            Ing_100         0,9 5012       877            Ing_1          47,2 5012       877            Ing_100         0,8 5012       879            Ing_1          46,6 5012       879            Ing_100         0,9 5012       880            Ing_1          43,6 5012       880            Ing_100         1,2   There are 100 ingredients per recipe/version. I'd like to display the data from this table like this:  RECIPE     INGREDIENT_Vxxx     PERCENTAGE_Vxxx     INGREDIENT_Vyyy     INGREDIENT_Vyyy (ETC) 4000       Ing_1               23,0                Ing_1               20,0 4000       Ing_100             0,1                 Ing_100              0,7   Because in different versions of recipes, ingredients could be removed or added, I'd like to display both ingredient and percentage per version per recipe. There's also the difficulty that different recipes have different number of versions.  I'm not even sure if this is possible at all or where to begin. Maybe with the PIVOT function?  Could anyone please point me in the right direction?
24577
For example, I want the first entry in Column B (B1) to show A5, then B2 shows A10, then B3 shows A15.  How would I go about doing this?
11546
Been looking to pivot rows (progress_check) into columns check 1, check 2 etc... No sum or totals required, just to display the results hopefully... Can anyone help, Thanks Ad
65893
Let T1 and T2 are DataTables with following fields  T1(CustID, ColX, ColY)  T2(CustID, ColZ)   I need the joint table  TJ (CustID, ColX, ColY, ColZ)   How this can be done in C# code in a simple way? Thanks.
61628
I would like to convert several range of columns into rows, and keeping the association to the original col/row source  Sample source sheet  --------------------------------------------------- | Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc...365 days | --------------------------------------------------- | name 1  |    25    |   30     |    5     | etc...|  --------------------------------------------------- | name 2  |    2     |          |    4     | etc...| --------------------------------------------------- | etc ... 100 rows   Expected Results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- | name 1  |  1/2/2018   |   30     |    --------------------------------------------------- | name 1  |  1/3/2018   |   5      |    --------------------------------------------------- | name 1  |  etc.. 365 days --------------------------------------------------- | name 2  |  1/1/2018   |   2      |    --------------------------------------------------- | name 2  |  1/2/2018   |          |    --------------------------------------------------- | name 2  |  1/3/2018   |   4      |    --------------------------------------------------- | name 2  |  etc.. 365 days --------------------------------------------------- | etc ... for each change of name pull all the col dates/values   Current results  --------------------------------------------------- | Data 1 |   Date      |  Value   |  --------------------------------------------------- | name 1  |  1/1/2018   |   25     |    --------------------------------------------------- |         |  1/2/2018   |   30     |    --------------------------------------------------- |         |  1/3/2018   |   5      |    --------------------------------------------------- |         |  etc.. 365 days                      I have to copy down to bring in "name 1" for each date  --------------------------------------------------- | name 2  |  1/1/2018   |   2      |             now update the formula to bring in the second item for the sequence of dates --------------------------------------------------- |         |  1/2/2018   |          |    --------------------------------------------------- |         |  1/3/2018   |   4      |    --------------------------------------------------- |         |  etc.. 365 days --------------------------------------------------- | etc ...     =Sheet1!A:A I can bring in the name from the source list, but I have to copy it down for the number of dates.  How can I have it match the number of dates?  and be dynamic to update as dates/values are added Edit: trying to use split and rept to count the number of times to repeat, then transpose.. =transpose(rept(split(arrayformula(concatenate(Sheet1!A:A&amp;"1")),"1",True, True),COUNTA(Sheet1!I1:ZZ1)))  =TRANSPOSE(Sheet1!$C$1:$Z$1) I can use transpose to get the dates/values, but it's fixed range.  How can I make it dynamic to import dates as they are added?
50351
I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office.  INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "London Office"); INSERT INTO MyTable VALUES ("Miranda", 126, "Bristol Office");   Can I insert all 4 rows in a single SQL statement?