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!
123183
I have the following query that sort of works, but I find it to be kludgy and inefficient.  Is there a better way to write this query?  I know this is a bit of an odd query, else I would probably have found some advice when searching, but it is necessary for our business logic.  We've been doing this in Hibernate queries and using software to output the file but we're needing to improve performance drastically and that means using raw sql.  The output of this query is to be used to partially populate a helper table that we query to figure out which of our 40-odd product tables a particular item is contained in.  Could the following query be replicated using something like GROUP_CONCAT in reverse? Note that the second and third column must be repeated on each line.  The fields all contain the same type of data.  SELECT 'Pn', 'Mfg', 'TableId', 'SourceColumn', 'OtherData' SELECT PN1, Mfg, '26', 'PN1', OtherData FROM Table  UNION ALL SELECT PN2, Mfg, '26', 'PN2', OtherData FROM Table UNION ALL SELECT PN3, Mfg, '26', 'PN3', OtherData FROM Table UNION ALL SELECT PN4, Mfg, '26', 'PN4', OtherData  INTO OUTFILE 'XXX.dat' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM Table d;   Input looks like the following table:       |  PN1  |  PN2  |  PN3    |  PN4    |  MFG  |  TableId  |  OtherData  |     -----------------------------------------------------------------------     |  asdf |  abde |  12354  |  asdfc  |  2    |  26       |  0.2456     |     |  nupo |  1354 |  null   |  null   |  2    |  26       |  1.53       |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |     |  ...  |  ...  |  ...    |  ...    |  ...  |  ...      |  ...        |   I want the output .dat file to look like this:       "Pn","Mfg","TableId","SourceColumn","OtherData"     "asdf",2,26,"PN1",0.2456     "abde",2,26,"PN2",0.2456     "12354",2,26,"PN3",0.2456     "asdfc",2,26,"PN4",0.2456     "nupo",2,26,"PN1",1.53     "1354",2,26,"PN2",1.53     ...     ...     ...   If I take the INTO OUTFILE clause out, I get an output that looks like this:       |  Pn      |  Mfg     |  TableId    |  SourceColumn  | OtherData  |     -----------------------------------------------------------------     |  "Pn"    |  {blob}  |  "TableId"  |  "PN"          |  {blob}    |     |  "asdf"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "abde"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  "12354" |  {blob}  |  "PN3"      |  "PN3"         |  {blob}    |     |  "asdfc" |  {blob}  |  "PN4"      |  "PN4"         |  {blob}    |     |  "nupo"  |  {blob}  |  "PN1"      |  "PN1"         |  {blob}    |     |  "1354"  |  {blob}  |  "PN2"      |  "PN2"         |  {blob}    |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |     |  ...     |  ...     |  ...        |   ...          |   ...      |
4938
Trying to find some simple SQL Server PIVOT examples.  Most of the examples that I have found involve counting or summing up numbers.  I just want to pivot some string data.  For example, I have a query returning the following.  Action1 VIEW   Action1 EDIT   Action2 VIEW   Action3 VIEW   Action3 EDIT     I would like to use PIVOT (if even possible) to make the results like so:  Action1 VIEW EDIT   Action2 VIEW NULL   Action3 VIEW EDIT     Is this even possible with the PIVOT functionality?
42072
I have a table called MyTable with varchar columns named SourceID and ReferenceID, where SourceID contains a table name and ReferenceID contains a key value for the source table. Is it possible in a query to refer to the source table's row assuming I know the name of the key column that matches the value in RefrenceID? Sample: SELECT     * FROM     MyTable as MT INNER JOIN MT.SourceID as Src ON MT.ReferenceID = Src.InstanceID  This doesn't work, obviously.  But is there a way to do this as opposed to dynamically building the SQL statement using the values of SourceID and ReferenceID and then executing the query?  More specifically, is there a way to make MyTable.SourceID in the example above be usable within the JOIN? Further considerations I found , and it has a trick like follows: select   E.EmployeeName, coalesce(s.store,o.office) as Location from   Employees E left outer join    Stores S on ... left outer join   Offices O on ... where   O.Office is not null OR S.Store is not null  The WHERE clause simply helps the outer joins behave like INNER JOINs.  This would work well enough if there were a limited number of table options and they all had UNIQUE constraints.  This gets me part of the way there but but I really want is to have the table names in the JOINs to be based on a column in Employees. I haven't given up yet. Any and all help is greatly appreciated.
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!
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?
21450
Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases  (PIVOT, ROWS to COLUMNS, TRANSPOSE &amp;c.) and all I get is  TSQL this, TSQL that and or Oracle this, Oracle that, proprietary function this, proprietary function that well, you get the picture.  I have a simple table structure - if it was any simpler, it'd be a list :-) - i.e. it's two columns.  stock_date       sku ----------       --- 2016-06-01         0 2016-06-02       123 2016-06-03       123 2016-06-04       123 2016-06-06       123 2016-06-07       123 2016-06-03       245 &lt;... &amp;c...&gt;   There may be many thousands of sku's but the date range will be at most a month or so, but can vary, so the code needs to cater for that.   So, I want to make the stock_date field a column.   sku     1   2   3   4   5   6   7 ---     -   -   -   -   -   -   -  123     0   Y   Y   Y   0   Y   Y 245     0   0   Y.... &amp;c.   Ideally, the code would be dynamic and would work with any server (even MySQL).   But if it has to use common table expressions and/or window functions, that's  OK but not proprietary crosstab or pivot functions.
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.
119927
How do I page results in SQL Server 2005?  I tried it in SQL Server 2000, but there was no reliable way to do this. I'm now wondering if SQL Server 2005 has any built in method?  What I mean by paging is, for example, if I list users by their username, I want to be able to only return the first 10 records, then the next 10 records and so on.  Any help would be much 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}
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
29756
How do you shade alternating rows in a SQL Server Reporting Services report?    Edit: There are a bunch of good answers listed below--from  and  to . Alas, I can choose only one...
39979
I have a simple problem when querying the SQL Server 2005 database. I have tables called Customer and Products (1->M). One customer has most 2 products. Instead of output as  CustomerName, ProductName ...  I like to output as   CustomerName, Product1Name, Product2Name ...  Could anybody help me?  Thanks!
74951
I am trying to produce 3 matrices on the same line. And on top of the each matrix, there is a name. I tried using tabular for the names and then I use align for the matrices. However, this creates some kind of space between the names and the matrices, which I do not want. Is there a better way to do this ? I have seen other posts but they do not seem to solve my problem. Please note that I need to use bordermatrix because I need to label the rows and columns as well.   \begin{center} \begin{tabular}{ccc} Kay &amp; Brian &amp;  Edwin \\ \end{tabular} \end{center} \begin{align*} \bordermatrix{ &amp; I &amp; A \cr I &amp;1 &amp; 2 \cr A &amp; 3 &amp; 4 }  &amp; \bordermatrix{ &amp; I &amp; A \cr I &amp;5 &amp; 6 \cr A &amp; 7 &amp; 8 }  &amp; \bordermatrix{ &amp; I &amp; A \cr I &amp;9 &amp; 10 \cr A &amp; 11 &amp; 12 }  \end{align*}
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}
44302
I have a data extension with a CustomerId(PK) that has multiple Productid's as Productid1, ProductId2, ProductId3...ProductId10.   Some of the Productid's are empty for some Customers and I'm trying to write some AMPscript if the productIdx is empty to show the next one.  I'm trying to display 6 products in an email and at the moment when I preview the email, if from the first six Products one doesn't have a value(is empty) I get the error "The subscriber preview failed to generate. Review the details, correct all issues, and try again. Product1 not found"  I want to skip the product that is NULL and go to the next one available.  %%[ SET @customerId = [customerId]  SET @customer_rows = LookupRows("PRL","customerId",@customerId) IF Rowcount(@customer_rows) &gt; 0 THEN SET @customer_row = ROW(@customer_rows, 1)  SET @product_count = 0  SET @productId1 = FIELD(@customer_row, "productId1")  IF NOT EMPTY(@productId1) THEN SET @product_rows = LookupOrderedRows("PI_UK", 1,"Normal_SalePrice Desc", "ProductCode", @productId1)  IF RowCount(@product_rows) &gt; 0 THEN  SET @product_count = ADD(@product_count,1) ELSE  SET @product_count = ADD(@product_count,0) ENDIF ELSE SET @product_count = ADD(@product_count,0) ENDIF    SET @productId2 = FIELD(@customer_row, "productId2") IF NOT EMPTY(@productId2) THEN SET @product_rows = LookupOrderedRows("PI_UK", 1,"Normal_SalePrice Desc", "ProductCode", @productId2) IF RowCount(@product_rows) &gt; 0 THEN SET @product_count = ADD(@product_count,1) ELSE SET @product_count = ADD(@product_count,0) ENDIF ELSE SET @product_count = ADD(@product_count,0) ENDIF  SET @productId3 = FIELD(@customer_row, "productId3") IF NOT EMPTY(@productId3) THEN SET @product_rows = LookupOrderedRows("PI_UK", 1,"Normal_SalePrice Desc", "ProductCode", @productId3) IF RowCount(@product_rows) &gt; 0 THEN SET @product_count = ADD(@product_count,1) ELSE SET @product_count = ADD(@product_count,0) ENDIF ELSE SET @product_count = ADD(@product_count,0) ENDIF  and so on till ProducId10...   I have uploaded a photo with the PRL Data extension
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
80451
I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped.  The table hierarchy is as follows:   Order table contains OrderReference(PO), OrderID OrderItems table contains Product/Lot/Qty requested (OrderID is parentID that joins these tables) Shipping contains OrderReference(PO), ShippingID ShippingArchive contains Product/Lot/Qty that was shipped (ShippingID is parent that joins these tables)   I want to create a report that looks as follows:  OrderReference#  Order Requested / Shipped Items / Missing Pieces  PC/Lot/Qty    -   PC/Lot/Qty    - PC/Lot/Qty   I have used a temp table to get some of the data but I realize I'm taking the wrong approach. I should be able to do all of this with a single query, though it will seemingly be a bit complex.   I get held up when I try to think about how to join where pieces don't exist, and where to perform the mathematics in my sorting and filtering. Please help!  Sincere thanks :)