I'm trying to create a product listing. Naturally Sql prints the data vertically forming rows after rows like this:  Rowa. product_img#1|Product_name|Product_price|Product_short_description Rowb. product_img#2|Product_name|Product_price|Product_short_description Rowc. product_img#3|Product_name|Product_price|Product_short_description Rowd. product_img#4|Product_name|Product_price|Product_short_description   and so on...  I'm trying to create a query so that I can display 3 products per rows like this:  Product_img#1|             Product_img#2|            Product_img#3| Product_name|              Product_name|             Product_name| Product_price|             Product_price|            Product_price| Product_description|       Product_description|      Product_description|   Looking up the web I found that what i'm trying to do is called pivot table. It was suggested to me to use a service report like SQL Server Reporting Services and create a Matrix Report to print the data the way I want it.  To be honest, I have no idea how to use those services and where to start... This is totally gibberish to me. I'm using Netbeans joined with Mysql. Where do I install those services and how do I use them?  Is there such kind of service integrated in Workbench? Any help/tricks/tips on how to achieve this would be gladly appreciated!
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?
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
105548
I make a new column in my attribute table and it has a default value (for example 0). I want to (probably using the field calculator) have the result that row 1 has the value 1, row 2=2 and so on, just like a numbered index.  I'd be thankful for a code example for the field calculator (including the use of $rownum (I think I need this for switching to the line)).
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!
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.
88090
Is there a way to fill the table with a row number so that count will restart as new group (identified by a separate column) starts? As a result, &quot;row number&quot; column will restart the count when the new attribute value for a group is found, and will produce row number (1,2,3 etc.) record within this group.  seems to answer my question, but SQL in QGIS does not allow such statements (DENSE_RANK) (or it does but I don't know how?).
17820
I am wondering if it is possible to use LINQ to pivot data from the following layout:  CustID | OrderDate | Qty 1      | 1/1/2008  | 100 2      | 1/2/2008  | 200 1      | 2/2/2008  | 350 2      | 2/28/2008 | 221 1      | 3/12/2008 | 250 2      | 3/15/2008 | 2150   into something like this:  CustID  | Jan- 2008 | Feb- 2008 | Mar - 2008 | 1       | 100       | 350       |  250 2       | 200       | 221       | 2150
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?
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
60813
I'm trying to write up a table but I'm missing the vertical rows in the last few columns. Anyone have any clues as to how to fix this?   \setlength\tabcolsep{4pt}  \begin{tabular}{|l|l|l|l|l|}  \cline{2-5}   \multicolumn{1}{l|}{} &amp; Autumn &amp; Pre-requisites &amp; Spring &amp; Pre-requisites \\\hline   \multirow{4}{*}{\textbf{Year 1}} &amp; MATH187 &amp; Either a mark of at least 80 in \\ &amp; &amp; MATH151 OR (in the NSW HSC \\ &amp; &amp; Examination) Mathematics Band \\ &amp; &amp; 4; or Mathematics Ext 1. &amp; MATH188 &amp; MATH187 \\\cline{2-5}                                &amp; MATH100 &amp; None &amp; MATH123 &amp; MATH187 or MATH141 with \\ &amp; &amp; &amp; &amp; co-requisites of MATH188 \\ &amp; &amp; &amp; &amp; or MATH142 \\\cline{2-5}                               &amp; CSIT110 &amp; None &amp; STAT101 &amp; None \\\cline{2-5}                                                                       &amp; ACCY111 &amp; None &amp; ACCY112 &amp; ACCY112 \\\hline \hline       \multirow{4}{*}{\textbf{Year 2}} &amp; MATH201 &amp; One of MATH110, MATH188 or \\ &amp; &amp; MATH283, or a mark of at least \\ &amp; &amp; 75 in MATH142 or MATH162, or \\ &amp; &amp; enrolment in course code 762A. &amp; MATH202 &amp; MATH201 \\\cline{2-5}                               &amp; MATH203 &amp; One of MATH101, MATH188 or \\ &amp; &amp; MATH283 or enrolment in course \\ &amp; &amp; code 762A. &amp; MATH204 &amp; MATH201 \\\cline{2-5}                               &amp; MATH222 &amp; One of MATH101, MATH188 or \\ &amp; &amp; enrolment in course code 762A &amp; MATH212 &amp; One of MATH101, MATH188, \\ &amp; &amp; &amp; &amp; MATH283 or enrolment in \\ &amp; &amp; &amp; &amp; course code 762A \\\cline{2-5}                                   &amp; STAT231 &amp; MATH188 or enrolment in \\ &amp; &amp; course code 762A. &amp; STAT232 &amp; STAT231 \\\cline{2-5}  \hline \end{tabular}   The output I get for the code above is:
2967
I want to have numbered listing in each row, I've tried with \rownumber, but it doesn't work. I wrote a number at the beginning, but Could it be automatically?  Can you help me? This is my code:  \documentclass[10pt, a4paper]{article}  \usepackage{fontspec} \setmainfont{Arial}  \usepackage{color, colortbl} \definecolor{Gray}{gray}{0.9}  \begin{document}  \begin{table}[h!] \tiny \begin{tabular}{|&gt;{\raggedright\arraybackslash}m{30mm}|&gt; {\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|&gt;{\raggedright\arraybackslash}m{5mm}|} \hline \multicolumn{1}{|&gt;{\centering\arraybackslash}m{30mm}|}{\rowcolor{Gray}\textbf{Text}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 1}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 2}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 3}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 4}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 5}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 6}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 7}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 8}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 9}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 10}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 11}} &amp; \multicolumn{1}{|&gt;{\centering\arraybackslash}m{5mm}|}{\textbf{Sem 12}}\\ \hline 1. Text text text text text text text text text text text text text &amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;\\\hline 2. Text text text text text text text text text text text text text &amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;\\\hline 3. Text text text text text text text text text text text text text  &amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;\\\hline 4. Text text text text text text text text text text text text text &amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;&amp;\\\hline  \hline \end{tabular} \end{table}  \end{document}
99266
I have a product details table, the fields in the table are *Prod_Id, Product_Name, Quantity, Slno*. Now i have created trigger for varchar data type field and implemented auto increment for Prod_Id (Primary key) and for Slno. The output will be  Prod_Id -->>>  PROD0001, PROD0002 and so on. Now if the product name is RACK it should create a id RACK_001 with the start two letters of the product name and auto increment automatically.  For all Product name it should create accordingly,how to do this.  As of now i am using a trigger for auto increment  in varchar data type. Help with the trigger code.  DELIMITER $$   CREATE TRIGGER tg_product_details_INSERT   BEFORE INSERT ON product_details   FOR EACH ROW    BEGIN      INSERT INTO product_details_seq VALUES (NULL);      SET NEW.Created_Date = NOW();      SET NEW.Submitted_Date = NOW();      SET NEW.Slno = coalesce((select max(Slno) from product_details), 0) + 1;      SET NEW.Prod_id = CONCAT((NEW.Prefix), LPAD(LAST_INSERT_ID(), 3, '0'));    END DELIMITER ;   product name -->> BOOK, SHOE, DRESS, FURNITURE  for all book it should create a autoincrement id BOOK_001, BOOK_002  and for all Shoe it should create a autoincrement id like SHOE_001, SHOE_002  and so on...!!!  Thanks, Acube.
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?
41703
I have the following code that produces the table that is shown. What I am trying to do is to get the fourth and fifth column titles vertically aligned at the top. Is this possible?  \documentclass{article} \usepackage{multirow} \usepackage{array}  \newcolumntype{x}[1]{% &gt;{\raggedright\hspace{0pt}}p{#1}}%  \begin{document} \centering \renewcommand{\arraystretch}{1.2} \begin{tabular}{| m{2cm} | p{2cm} | p{2cm} | p{2cm} | p{2cm} | p{2cm} |} \hline     \multirow{3}{1.7cm}{Data Set} &amp;%     \multicolumn{3}{c |}{Raw Data} &amp;%     Offline Processing Time &amp;     Index Size (KB)\\ \cline{2-4} %     &amp; %     RDF N3 File SizeKB (sec)    &amp;%     Number of Triples &amp;%     Number of Entities\\    \hline \end{tabular} \end{document}
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
112426
Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values.  CREATE TEMP TABLE foo AS SELECT x::text AS name, x::int FROM generate_series(1,10) AS t(x);  CREATE TEMP TABLE bar AS SELECT x::text AS name, x::int FROM generate_series(1,5) AS t(x);   For example, if that function were multiplication, how would I generate a (multiplication) table like the one below,    All of those (arg1,arg2,result) rows can be generated with   SELECT foo.name AS arg1, bar.name AS arg2, foo.x*bar.x AS result FROM foo CROSS JOIN bar;    So this is only a question of presentation, I would like this to also work with a custom name -- a name that is not simply the argument CASTed to text but set in the table,  CREATE TEMP TABLE foo AS SELECT chr(x+64) AS name, x::int FROM generate_series(1,10) AS t(x);  CREATE TEMP TABLE bar AS SELECT chr(x+72) AS name, x::int FROM generate_series(1,5) AS t(x);   I think this would be easily do-able with a CROSSTAB capable of a dynamic return-type.  SELECT * FROM crosstab(   '     SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x     FROM foo     CROSS JOIN bar   ', 'SELECT DISTINCT name FROM bar' ) AS **MAGIC**   But, without the **MAGIC**, I get   ERROR:  a column definition list is required for functions returning "record" LINE 1: SELECT * FROM crosstab(    For reference, using the above examples with names this is something more like what tablefunc's crosstab() wants.  SELECT * FROM crosstab(   '     SELECT foo.x AS arg1, bar.x AS arg2, foo.x*bar.x     FROM foo     CROSS JOIN bar   ' ) AS t(row int, i int, j int, k int, l int, m int);   But, now we're back to making assumptions about the content and size of the bar table in our example. So if,   The tables are of undefined length, Then the cross-join represents a cube of undefined dimension (because of above), The catagory-names (cross-tab parlance) are in the table   What's the best we can do in PostgreSQL without a "column definition list" to generate that kind of presentation?
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?